Import CSV to Excel Using VBA: A Practical Guide

To import a CSV file into Excel with VBA, use Workbooks.OpenText to parse the file, then copy its values into a worksheet in the workbook that contains your macro. The example below opens a file picker, imports comma-delimited UTF-8 data into a sheet named ImportedData, and closes the CSV workbook.

What VBA does when it imports a CSV

A CSV (comma-separated values) file stores rows of data as text, with separators between fields. It does not retain Excel features such as formulas, cell formatting, multiple worksheets, or charts. When Excel opens a CSV, it interprets the text and displays the result in a worksheet. VBA can automate that process so a repeatable task does not require manually choosing and opening a file each time.

The important step is choosing how Excel parses the file. Workbooks.OpenText lets the macro specify details such as the delimiter, text qualifier, and character encoding. After the file opens as a temporary workbook, VBA can copy its cells into a worksheet in the destination workbook. This is different from pasting CSV text into a single cell and asking Excel to split it afterward.

The example is for Excel for Microsoft 365 on Windows. It imports one CSV at a time and assumes commas separate fields. If your file uses tabs, semicolons, or another delimiter, adjust the import settings rather than relying on Excel to guess.

VBA macro to import a CSV into a worksheet

Put this macro in a standard VBA module in the workbook that should receive the data. The destination sheet is called ImportedData; the macro creates it if it does not already exist. Save a copy of the workbook before running any macro: VBA changes cannot be reversed with Ctrl+Z.

Sub ImportCsvToImportedData()
    Dim targetBook As Workbook
    Dim targetSheet As Worksheet
    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim sourceRange As Range
    Dim picker As Object
    Dim csvPath As String

    ' The workbook containing this macro receives the imported data.
    Set targetBook = ThisWorkbook

    ' Ask the user to choose a CSV file.
    Set picker = Application.FileDialog(3)
    With picker
        .Title = "Select a CSV file"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "CSV files", "*.csv"
        If .Show <> -1 Then Exit Sub
        csvPath = .SelectedItems(1)
    End With

    ' Find the destination sheet, or create it if needed.
    On Error Resume Next
    Set targetSheet = targetBook.Worksheets("ImportedData")
    On Error GoTo 0
    If targetSheet Is Nothing Then
        Set targetSheet = targetBook.Worksheets.Add( _
            After:=targetBook.Worksheets(targetBook.Worksheets.Count))
        targetSheet.Name = "ImportedData"
    End If

    ' Open as UTF-8 text, split fields at commas, and respect quoted fields.
    Workbooks.OpenText Filename:=csvPath, Origin:=65001, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, Local:=False

    Set sourceBook = ActiveWorkbook
    Set sourceSheet = sourceBook.Worksheets(1)
    Set sourceRange = sourceSheet.UsedRange

    ' Replace old cell contents, then transfer values without CSV formatting.
    targetSheet.Cells.ClearContents
    targetSheet.Range("A1").Resize(sourceRange.Rows.Count, _
        sourceRange.Columns.Count).Value = sourceRange.Value

    sourceBook.Close SaveChanges:=False
    targetBook.Activate
End Sub

To add it, press Alt+F11 in Excel for Windows, choose Insert > Module in the Visual Basic Editor, paste the code, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Return to Excel and run it from Developer > Macros, or assign it to a button. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

How the macro works

ThisWorkbook identifies the workbook in which the macro code is stored, so it remains the destination even after Excel opens the CSV. The file picker lets you choose the source file at run time. If you cancel the picker, the procedure exits without importing anything.

The OpenText arguments specify UTF-8 encoding, comma separation, and double quotes as text qualifiers. A text qualifier allows a field to contain a comma without being split into extra columns. For example, a CSV row such as 104,"Portland, Oregon",Active should become three fields, not four. The macro then writes the imported range’s values into cell A1 and closes the temporary source workbook without saving it.

Before writing, the macro clears cell contents on ImportedData. This prevents leftover rows from a previous, longer import from appearing to be part of the new data. It leaves cell formatting in place, but it also removes any formulas or manually entered values on that sheet. Use a dedicated import sheet, or change the destination name and clearing behavior to fit your workbook.

Choose the right import settings

CSV files do not have one universal layout. Confirm the delimiter, encoding, and expected column types before automating a recurring import. A file exported from one system may use a different convention from a file exported from another.

Delimiter: The sample sets Comma:=True and disables tabs, semicolons, spaces, and other delimiters. For a semicolon-delimited file, set Comma:=False and Semicolon:=True. Do not enable multiple delimiter options unless the file actually uses them.
Text qualifier: Double quotes are common. They mark a field as one value even when it contains a delimiter. If the source uses a different quoting convention, verify the result rather than assuming the default is suitable.
Encoding: The example uses code page 65001 for UTF-8, which is common for modern exports. If the source is encoded differently, accented characters or symbols may appear incorrectly; use the code page appropriate to the source.
Headers: The macro imports the first row as ordinary data. It does not create Excel table headers or apply formatting. If you want a table, add a separate table-creation step after confirming that the first row contains unique column names.

For a fixed, well-understood export, explicit settings make the process predictable. For files whose format changes between sources, consider asking the user for the file type or checking the first line before importing. A macro cannot safely infer every delimiter and quoting rule from every CSV.

Watch for automatic data conversion

Excel may interpret fields as numbers, dates, or other values when it opens a CSV. That can be useful for amounts and dates, but it can damage identifiers. A customer code such as 00127 may become 127; a long number may be displayed in scientific notation or lose precision; and text that resembles a date may be converted to a date value.

If leading zeros or exact text are essential, do not assume that copying the data after opening the CSV will restore the original text. The conversion has already happened during import. One option is to use the FieldInfo argument of OpenText to assign text format to specific columns. For example, a field-information array can mark column 1 as text with the Excel VBA constant xlTextFormat (value 2). The array must cover the columns that need protection, so this approach works best when the CSV schema is known and stable.

Another option is Power Query, which can provide a more visible import-and-transform workflow and let you set column types before loading. For a small automated task, VBA may be convenient; for repeatable imports with changing files or multiple cleanup steps, Power Query is often easier to inspect and maintain.

Common problems and practical fixes

Everything appears in one column: Check that the delimiter argument matches the file. Also inspect the original CSV in a text editor; some exports use semicolons or tabs rather than commas.
Text splits into too many columns: Look for delimiter characters inside fields and confirm that quoted fields are handled correctly. The sample uses double quotes as the text qualifier.
Characters look garbled: Check the file’s encoding. UTF-8 is common, but not guaranteed. A mismatch between the actual encoding and Origin can corrupt how text is displayed.
Zeros disappear or dates change: This is usually Excel’s automatic type conversion, not a copying error. Import sensitive columns as text or use a workflow where you can assign column types before loading.
The wrong workbook receives data: Keep the macro in the workbook intended as the destination. The sample uses ThisWorkbook, not ActiveWorkbook, because opening the CSV changes which workbook is active.
Old rows remain after a shorter import: The provided macro clears existing contents first. If you remove that line, clear the old data range another way or stale records may remain below the new rows.

For troubleshooting, test with a small copy of the source file and inspect both the first and last rows. Check columns containing commas, quotes, leading zeros, dates, and long IDs; these are more likely to expose parsing or conversion problems than simple numeric columns.

Safety, file access, and alternatives

Macros can change workbook data, so keep a backup and use only files you trust. Excel may block macros in files downloaded from the internet or received by email. Enable macros only when you understand what the code does and trust its source; changing security settings broadly to make one file run is not a good substitute for checking it.

The file picker in the example avoids hard-coding a path, but it requires a person to select a file each time. For a scheduled or unattended process, a fixed path or a controlled folder scan may be more appropriate. Such automation should also account for missing files, unexpected filenames, and duplicate imports rather than silently processing the wrong file.

On Windows, Alt+F11 opens the VBA editor and Alt+F8 opens the macro list. On Mac, VBA is available in desktop Excel, but menus, security prompts, and keyboard shortcuts can differ; use Tools > Macro > Visual Basic Editor or the Mac-specific shortcut shown by your Excel installation. Excel for the web cannot create, run, or edit VBA macros. A workbook containing VBA can be stored online, but the macro must be run in desktop Excel.

For one-off imports, Excel’s Data > From Text/CSV command is a straightforward alternative and provides a preview before loading. Power Query is useful when the same cleanup steps need to be repeated. VBA is most useful when importing is one step in a larger desktop Excel process, such as refreshing a report or placing data into a predefined workbook layout.

Frequently Asked Questions

Can VBA import a CSV without opening it visibly?
Yes. VBA can open and process a CSV workbook in the background by changing Excel’s screen-updating behavior or by using other import approaches. The example opens the file through Excel’s text-import process and closes it after transferring values; it does not rely on displaying a separate window to the user.

How do I import a CSV into a specific sheet?
Set the destination worksheet variable to the desired sheet in the workbook containing the macro, then write the imported range to that sheet. In the sample, the destination is named ImportedData, and data begins at A1. Change the sheet name or starting cell to suit your layout.

Can the macro import several CSV files?
Yes. The file picker can allow multiple selections, or VBA can loop through matching files in a folder. For a batch process, decide whether each file should go to a separate worksheet or be appended below the previous file, and verify that the columns have the same structure before combining them.

Why does Excel remove leading zeros from my CSV?
Excel has interpreted the field as a number. Import that column as text before conversion occurs, for example by specifying its type with FieldInfo in OpenText. Formatting the cell as text after the number has already been converted does not reliably recover the original zeros.

Can I run this VBA macro in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro; the sample’s exact import behavior is written for Excel for Microsoft 365 on Windows.

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.