Import CSV to Excel Using VBA: A Safe, Repeatable Method

To import a CSV file into Excel with VBA, use Workbooks.OpenText to parse the file, then copy its data to a worksheet in your macro-enabled workbook. The example below lets you choose a file, imports comma-delimited UTF-8 text, and places the result on a sheet named CSV Import.

A basic VBA macro for importing a CSV

First, create a worksheet named CSV Import in the workbook that will hold the macro. Save that workbook as an Excel Macro-Enabled Workbook (.xlsm). The macro clears the destination sheet before each import, so its previous contents will be replaced.

Sub ImportCsvToSheet()
    Dim filePath As Variant
    Dim csvBook As Workbook
    Dim targetSheet As Worksheet
    Dim sourceRange As Range
    Dim oldScreenUpdating As Boolean

    On Error GoTo ImportError

    ' Ask the user to choose a CSV file.
    filePath = Application.GetOpenFilename( _
        "CSV Files (*.csv),*.csv", , "Select a CSV file")
    If filePath = False Then Exit Sub

    Set targetSheet = ThisWorkbook.Worksheets("CSV Import")
    oldScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False

    ' Open as UTF-8 text and split fields at commas.
    Workbooks.OpenText Filename:=CStr(filePath), Origin:=65001, _
        DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, Local:=True

    Set csvBook = ActiveWorkbook
    Set sourceRange = csvBook.Worksheets(1).UsedRange

    ' Replace the previous import with the new data.
    targetSheet.Cells.Clear
    sourceRange.Copy Destination:=targetSheet.Range("A1")

    csvBook.Close SaveChanges:=False
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "CSV import complete.", vbInformation
    Exit Sub

ImportError:
    On Error Resume Next
    If Not csvBook Is Nothing Then csvBook.Close SaveChanges:=False
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "Import failed: " & Err.Description, vbExclamation
End Sub

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and this one clears the destination sheet. In the Visual Basic Editor, use Insert > Module to add a standard module, paste the code, and run ImportCsvToSheet. In Excel for Microsoft 365 on Windows, Alt+F11 opens the editor; on Mac, Option+F11 or Fn+Option+F11 may be needed, depending on the keyboard.

What the macro does, step by step

GetOpenFilename opens a file-selection dialog and returns the chosen path. If the user cancels, the procedure exits without changing the destination. Workbooks.OpenText then opens the CSV as a separate workbook and applies explicit parsing options. The macro takes the used range on its first worksheet, clears the target sheet, copies the imported cells to A1, and closes the temporary CSV workbook without saving changes.

ThisWorkbook means the workbook containing the macro, not whichever workbook happens to be active. That distinction matters: opening a CSV changes the active workbook. The code stores the CSV workbook in csvBook and refers to the destination by its fixed sheet name, reducing the risk of copying data to the wrong file.

The error handler attempts to close the temporary workbook and restore screen updating if an import fails. It does not make every possible failure harmless: for example, the target sheet must exist and must not be protected against the changes the macro makes. The message box reports the error so you can investigate rather than assuming the import succeeded.

Set up and run the macro safely

Open the workbook where the imported data should go. Add a worksheet and name it exactly CSV Import.
Save the workbook as Excel Macro-Enabled Workbook (*.xlsm). A standard .xlsx file cannot store VBA code.
Open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module.
Return to Excel and run the macro from Developer > Macros, or assign it to a button if you will use it regularly.
Choose a CSV file in the dialog and check the imported rows, headers, dates, and identifiers before relying on the result.

If the Developer tab is not visible in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. Only enable macros for workbooks you trust; do not lower macro security globally just to run an unknown file. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Excel for Mac supports VBA, but file dialogs, file paths, and some import behavior can differ, so validate this Windows-oriented example on a Mac before adopting it.

Choose the right delimiter and text settings

A CSV is a plain-text file containing fields separated by a delimiter. Despite the name, the delimiter is not always a comma: some systems produce semicolon-separated files, tab-separated text, or files with another separator. The macro explicitly sets Comma:=True and Semicolon:=False. If the source uses semicolons, switch those settings so that Comma:=False and Semicolon:=True. For another delimiter, use the Other option and specify the character with the appropriate OtherChar argument.

The text qualifier is the double quote. This is important for a field such as "Seattle, WA": the comma inside the quoted field belongs to the address, not the boundary between columns. A correctly formatted CSV also represents a quote inside a quoted field by doubling it. If the source uses a different quoting convention, the import settings may need adjustment.

Origin:=65001 requests UTF-8, which helps preserve characters such as accented letters when the file was saved in UTF-8. It cannot identify every possible encoding correctly. If characters appear garbled, confirm the file’s actual encoding with the system that created it and select or specify the matching encoding. Local:=True tells Excel to use local settings for aspects of text conversion; date and decimal interpretation can consequently differ with regional settings.

Protect dates, leading zeros, and other values

Opening a CSV is not the same as loading a typed Excel table. Excel may infer that text looks like a number, date, or scientific notation and convert it. That can be convenient for a sales amount such as 19.95, but damaging for a product code such as 001245, which may lose its leading zeros. A value like 03/04/2026 can also be interpreted differently depending on regional date conventions.

The sample macro uses Excel’s normal text-import behavior; it does not force every column to text or assign a schema. For repeatable business imports, decide the intended type of each column first. Preserve postal codes, account numbers, and identifiers as text; parse dates using a known format; and treat quantities or prices as numbers only when appropriate. For a one-off import, use Excel’s import preview or Power Query to inspect and set column types before loading.

Take care with CSV files from untrusted sources. Text that begins with characters Excel recognizes as a formula can be treated as executable spreadsheet content by some import or opening workflows. Do not assume that a CSV is safe merely because it is plain text. Review unexpected formulas and use a controlled import process that treats untrusted fields as text when appropriate, especially before sharing the resulting workbook.

Common problems and practical fixes

“Subscript out of range”: Check that the destination worksheet is named exactly CSV Import. A trailing space or different spelling is enough to cause this error.
Everything appears in one column: The delimiter in the macro may not match the file. Check whether the source uses commas, semicolons, tabs, or another character.
Accented characters look wrong: Check the CSV’s encoding. The example requests UTF-8; change the import origin if the file uses a different encoding.
Dates or numbers change unexpectedly: Review regional settings and the source format, then import with explicit column types when those values must remain consistent.
Old data disappears: This is expected because targetSheet.Cells.Clear clears the destination before copying. Remove that line if you want to append instead, but then choose a method to find the next available row and prevent duplicate imports.
The macro is blocked: Confirm that the workbook is saved as .xlsm and that the file is trusted under your organization’s security policy. Do not enable macros in an unfamiliar workbook just to bypass a warning.

For large CSV files, copying the entire used range may take time and can use substantial memory. Excel worksheets also have a limit of 1,048,576 rows and 16,384 columns. A file exceeding the worksheet row limit cannot be placed in one sheet in full; use a database, Power Query, or another data-processing tool suited to the file size.

VBA versus Power Query for recurring imports

VBA is useful when an import is part of a button-driven workflow, needs custom actions, or must run alongside other workbook automation. It gives you control over file selection and destination handling, but the macro must be maintained, trusted, and reviewed for changes in the source format.

Power Query is often a better fit for repeatable data cleanup and refresh. In Microsoft 365 for Windows, start from Data > Get Data > From File > From Text/CSV, inspect the delimiter and preview, then choose Load or Transform Data. Power Query records transformation steps and can refresh the query when the source changes, which makes column typing and repeatable cleanup more visible than relying on automatic conversion during a basic VBA open. On Mac, Power Query availability and connectors can vary by Excel release; Excel for the web does not offer the full Windows authoring experience.

For a one-time file, the ordinary Data > From Text/CSV interface may be simpler than writing code. For a controlled recurring workflow that includes other Excel actions, VBA may be appropriate. Choose based on whether you need a refreshable transformation, a small automation, or processing beyond Excel’s worksheet limits.

Frequently Asked Questions

Can VBA import a CSV without opening it as a separate workbook?
Yes. Other approaches, including a QueryTable or Power Query, can load text into a worksheet or data model without using this exact open-and-copy pattern. They require different setup and are worth considering when you need refreshable imports or explicit column typing.

How do I import a semicolon-delimited CSV?
In the example’s OpenText call, set Comma:=False and Semicolon:=True. Confirm the file really uses semicolons as field separators, since punctuation may also appear inside quoted text.

Will this macro keep leading zeros?
Not reliably. Excel can interpret a digit-only field as a number and remove leading zeros. If those zeros are significant, import the relevant column as text using a method that assigns its data type before loading, then verify the result.

Can I append the CSV rows instead of replacing the old import?
Yes, but the sample deliberately clears the sheet. To append, remove the clear operation and calculate the first empty destination row before copying. Also check whether the incoming file repeats headers or contains rows already imported.

Can I run this VBA macro in Excel for the web?
No. Excel for the web can display and edit many workbook contents, but VBA macros run in the desktop Excel application. Open the macro-enabled workbook in desktop Excel to run the procedure.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.