How to Import CSV to Excel with a VBA Macro: The Complete One-Click Guide (2026)

Learning how to import CSV to Excel with VBA macro automation turns one of the most repetitive chores in spreadsheet work into a single click. If you regularly receive CSV exports — bank statements, Shopify orders, ad-platform reports, payroll files — you already know the drill: open the file, fix the delimiter, fix the encoding, fix the dates, save as XLSX, repeat tomorrow. A small reusable macro does all of that for you, the same way, every time. This guide gives you the complete, copy-paste-ready code for a VBA import CSV to Excel workflow, explains every line so you can adapt it, and covers the two things that trip up most people: the vba csv import delimiter settings and macro security.

By the end you will have a macro that asks you to pick a CSV file, imports it cleanly into a new worksheet with the correct delimiter, autofits the columns, and confirms the row count — plus a second macro that loops through an entire folder and imports every CSV at once. You will also know when an excel macro convert csv to xlsx job is better handled by Power Query instead, how to schedule the import so it runs while you sleep, and what to do when Excel refuses to run your code. No prior VBA experience is assumed; if you can copy and paste, you can do this.

Why import CSV to Excel with a VBA macro instead of doing it by hand

Double-clicking a CSV file is fast, but it is also a gamble. Excel opens CSVs using your system’s regional guesses: the wrong delimiter collapses every row into column A, the wrong encoding turns names into mojibake, and text columns like ZIP codes or phone numbers silently lose their leading zeros. Do this once and it is a minor annoyance. Do it every morning with a fresh export from your store, your bank, or your ad account, and it becomes a real tax on your time — and a source of quiet data errors that nobody notices until the monthly report looks wrong.

A VBA macro fixes the process instead of repeating it. When you import CSV to Excel with VBA macro automation, you declare the rules once — comma or semicolon delimiter, UTF-8 encoding, text qualifiers, which columns stay as text — and the macro applies them identically to every file. The result is consistent: the January export and the June export land in your workbook structured exactly the same way, which is precisely what pivot tables, VLOOKUPs, and charts need to keep working without babysitting.

There is a second, less obvious benefit: when you import CSV to Excel with VBA macro techniques as part of an excel macro convert csv to xlsx pipeline, the process is auditable. When a colleague asks “how did this workbook get built?”, the answer is the code itself, not a half-remembered sequence of clicks. Finance teams across the US, UK, India, Canada, and Australia use exactly this pattern for recurring reconciliations, because the macro doubles as documentation. And for a one-off conversion where you do not want to touch code at all, the free CSV to Excel converter on our homepage does the job in your browser with nothing to install.

What you need before you write the macro

Three quick setup steps, each taking under a minute. First, show the Developer tab: go to File › Options › Customize Ribbon and tick the Developer checkbox. This gives you the Visual Basic button that opens the VBA editor. Second, save your workbook as a macro-enabled workbook (.xlsm) via File › Save As — a plain .xlsx file silently strips out any macro you add, which is the single most common “my macro disappeared” mystery. Third, decide how you want Excel to treat macros via File › Options › Trust Center › Trust Center Settings › Macro Settings.

For writing and testing your own code, “Disable all macros with notification” is the right choice: Excel will show a security banner each time you open the workbook, and you click Enable Content because you know the code is yours. Never choose “Enable all macros” permanently — that setting applies to every workbook, including ones emailed to you by strangers. We will come back to macro safety at the end of this guide.

One platform note: VBA is a Windows-first technology. The macro below runs on desktop Excel for Windows in every modern version (Excel 2016 through Microsoft 365). Excel for Mac does have VBA, but the file-picker dialog and some import behaviors differ — see the FAQ for the Mac caveats. Excel for the web cannot run VBA at all; if your team lives in the browser, Power Query or Office Scripts are the alternatives.

How to import CSV to Excel with a VBA macro: the complete code

Here is the full macro. It asks you to choose a CSV file, creates a fresh worksheet, imports the file with explicit delimiter control, autofits the columns, and reports how many rows came in. Copy everything between the Sub lines:

Sub ImportCSVtoExcel()
    Dim csvFile As Variant
    Dim ws As Worksheet
    Dim qt As QueryTable

    ' --- Step 1: let the user pick the CSV file ---
    csvFile = Application.GetOpenFilename( _
        FileFilter:="CSV Files (*.csv), *.csv", _
        Title:="Select a CSV file to import")

    If csvFile = False Then Exit Sub   ' user pressed Cancel

    ' --- Step 2: add a fresh worksheet for the data ---
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "CSV_Import"

    ' --- Step 3: import with explicit delimiter control ---
    Set qt = ws.QueryTables.Add( _
        Connection:="TEXT;" & csvFile, _
        Destination:=ws.Range("A1"))

    With qt
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .Refresh BackgroundQuery:=False
    End With

    ' --- Step 4: tidy up ---
    ws.Columns.AutoFit
    MsgBox "Imported " & ws.UsedRange.Rows.Count & _
           " rows from " & csvFile, vbInformation, "Done"
End Sub

1. Press Alt+F11 to open the Visual Basic editor.
2. Go to Insert › Module to create a blank code module.
3. Paste the macro above into the module window.
4. Press Ctrl+S, then close the editor and save the workbook as .xlsm.
5. Back in Excel, press Alt+F8, select ImportCSVtoExcel, and click Run.
6. Pick any CSV file. A new CSV_Import sheet appears with your data, columns autofitted.

Want it truly one-click? Assign the macro to a button: Developer › Insert › Button (Form Control), draw it on any sheet, and pick ImportCSVtoExcel when Excel asks which macro to assign. Or give it a keyboard shortcut via Alt+F8 › Options. From then on, importing a CSV is one click or one keystroke — no wizard, no dialogs, no repeated setup.

Understanding the VBA CSV import code line by line

You do not need to memorize VBA to use this macro, but understanding the moving parts lets you adapt it — and adaptation is where a vba import csv to excel routine earns its keep. Here is what each block does.

The file picker

Application.GetOpenFilename shows the standard Windows file dialog filtered to *.csv. It returns the full path as text, or False if the user cancels — hence the If csvFile = False Then Exit Sub guard. Using a picker instead of a hardcoded path is what makes this a reusable macro: it works on any CSV, not just the one you tested with.

The destination worksheet

ThisWorkbook.Worksheets.Add creates a new sheet in the workbook that holds the macro (ThisWorkbook, as opposed to ActiveWorkbook, which means “whichever workbook happens to be on top”). Naming it CSV_Import keeps things tidy. If you run the macro twice you will get an error on the duplicate name — a simple fix is to stamp the time into the name, e.g. ws.Name = "CSV_" & Format(Now, "hhmmss").

The QueryTable import engine

QueryTables.Add with a "TEXT;" connection is Excel’s built-in text importer — the same engine behind Data › From Text/CSV — driven from code. The critical lines are the delimiter declarations: .TextFileParseType = xlDelimited says “this file uses a delimiter”, and .TextFileCommaDelimiter = True says “the delimiter is a comma”. .TextFileTextQualifier = xlTextQualifierDoubleQuote tells Excel that fields wrapped in double quotes (like "Smith, John") are single values even though they contain a comma — this is the RFC 4180 quoting rule, and getting it right is what separates a clean import from a mangled one. .Refresh BackgroundQuery:=False runs the import immediately and waits for it to finish before moving on.

The tidy-up

ws.Columns.AutoFit resizes every column to fit its content, and the MsgBox confirms the row count so you can sanity-check against what you expected. That confirmation message has caught more than one truncated export in real-world use — if you expected 48,000 rows and got 12, something went wrong upstream.

Customizing the import: delimiters, encoding, and column formats

The default macro assumes a comma-delimited file, which covers CSVs from the US, UK, India, Canada, and Australia. But a large share of the world’s CSVs use something else, and the vba csv import delimiter settings are the first thing to customize. Here are the three most common variants — change only the delimiter lines inside the With qt block:

Semicolon-delimited files (Europe, Brazil)

CSVs from Germany, France, Italy, Spain, the Netherlands, and Brazil typically use semicolons, because those locales use the comma as the decimal separator. Swap the delimiter lines for:

        .TextFileCommaDelimiter = False
        .TextFileSemicolonDelimiter = True

Tab-delimited or pipe-delimited files

        .TextFileCommaDelimiter = False
        .TextFileTabDelimiter = True        ' for TSV files
        ' --- or, for pipe-separated files: ---
        .TextFileOtherDelimiter = True
        .TextFileOtherDelimiterCharacter = "|"

Keeping leading zeros and long numbers intact

Excel loves to “help” by converting anything that looks numeric — stripping leading zeros from ZIP codes like 02134, and mangling 16-digit card or ID numbers into scientific notation. For columns that must stay as text, declare their data types with .TextFileColumnDataTypes, passing an array with one entry per column (xlTextFormat = 2 forces text):

        ' Force columns 1 and 3 to Text, let the rest auto-detect
        .TextFileColumnDataTypes = Array(2, 1, 2, 1, 1)

Our dedicated guide to keeping leading zeros when importing CSVs covers the manual-import side of this problem in depth.

A note on encoding

The QueryTable importer reads the file using your system’s default encoding unless the file carries a BOM (byte-order mark). UTF-8 files exported from modern systems usually import fine; legacy exports from older Windows software or some accounting packages may need attention. If accented characters arrive garbled, the most reliable fix is to re-save the CSV as UTF-8 with BOM before running the macro — RFC 4180, the closest thing CSV has to an official spec, and Wikipedia’s CSV overview both document the format’s encoding quirks. For a full treatment, see our guide to CSV encoding in Excel.

Batch import: loop through a whole folder of CSV files with VBA

Once you import CSV to Excel with VBA macro code one file at a time, the natural next step is a whole folder at once. This second macro asks you to pick a folder, then imports every *.csv in it — each into its own worksheet named after the file. It is the same engine as before, wrapped in a Dir() loop:

Sub BatchImportCSVFolder()
    Dim folderPath As String
    Dim csvFile As String
    Dim ws As Worksheet
    Dim qt As QueryTable

    ' --- Pick the folder ---
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the folder containing your CSV files"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1) & "\"
    End With

    Application.ScreenUpdating = False   ' speed up the loop
    csvFile = Dir(folderPath & "*.csv")

    Do While csvFile <> ""
        Set ws = ThisWorkbook.Worksheets.Add
        ' Sheet names max out at 31 characters
        ws.Name = Left(Replace(csvFile, ".csv", ""), 31)

        Set qt = ws.QueryTables.Add( _
            Connection:="TEXT;" & folderPath & csvFile, _
            Destination:=ws.Range("A1"))
        With qt
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .Refresh BackgroundQuery:=False
        End With
        ws.Columns.AutoFit

        csvFile = Dir   ' next file
    Loop

    Application.ScreenUpdating = True
    MsgBox "Batch import complete.", vbInformation
End Sub

Two practical notes. First, Application.ScreenUpdating = False stops Excel redrawing the screen for every sheet and makes the loop dramatically faster on large folders — just be sure to set it back to True at the end (or it stays off until you restart Excel). Second, sheet names cannot exceed 31 characters and cannot contain characters like / \ ? * [ ]; the Left(..., 31) handles length, and if your filenames contain those symbols you will want to strip them too. Prefer everything on one sheet instead? Find the last used row and point each import’s destination there, skipping headers after the first file — the FAQ below has the details.

VBA macro vs Power Query: which CSV import method should you use?

VBA is not the only automation game in town. Power Query (Data › Get Data › From Text/CSV) is Microsoft’s modern import tool, and for many recurring imports it is the better choice. Here is an honest comparison:

Choose the VBA macro when you need a portable, one-click button inside a specific workbook; when the import needs custom logic Power Query cannot express (conditional sheet naming, MsgBox confirmations, writing to very specific ranges); or when you are distributing a template to colleagues who just need to press a button. VBA travels with the .xlsm file, no explanation needed.

Choose Power Query when the import needs transformation steps — removing blank rows, unpivoting, merging files — because Power Query records each step and replays it on refresh; when the source file updates in place and you want one-click refresh rather than re-import; or when you work in Excel for the web or on a Mac, where VBA is limited or absent. Our guide to importing CSV with Power Query walks through that flow.

They are not enemies, either. A common professional pattern is a VBA button that triggers a Power Query refresh (ThisWorkbook.Connections("Query - sales").Refresh), giving users one friendly button over a robust query. And if your needs are simpler than either — a single file, right now, no code — the free CSV to Excel converter remains the fastest path from CSV to XLSX.

Troubleshooting: when your VBA CSV import macro won’t run

“Macros have been disabled” banner. Click Enable Content — you wrote this code, so it is safe. If the banner does not appear and nothing happens, check File › Options › Trust Center › Macro Settings; “Disable all macros without notification” silently blocks everything.

The macro vanished after saving. You saved as .xlsx. Excel strips VBA code from .xlsx files without much warning. Re-paste the code and save as .xlsm (macro-enabled workbook).

Everything lands in one column. Delimiter mismatch — the classic vba csv import delimiter problem. Open the CSV in Notepad, check whether values are separated by commas, semicolons, tabs, or pipes, and set the matching TextFile property.

Run-time error 1004 on the QueryTable line. Usually a bad file path (the picker was cancelled but the guard was removed) or a file that is open in another program with an exclusive lock. Close the CSV everywhere else and try again.

Duplicate sheet-name error on the second run. The batch macro names sheets after files; running it twice collides. Either delete the old sheets first or stamp names with the time.

It works on Windows but not on a colleague’s Mac. The folder-picker dialog and some QueryTable defaults differ on macOS. Have the Mac user test on a copy, or standardize the team on Power Query for shared templates.

Import is slow on huge files. QueryTables handle hundreds of thousands of rows fine, but screen redraw and AutoFit on every column cost time. Keep ScreenUpdating = False during the loop and consider autofitting only the used range. For files brushing Excel’s 1,048,576-row limit, see our guide to large CSV files in Excel.

A word on macro safety: only run code you trust

Macros are powerful precisely because they can do anything you can do in Excel — and a few things you cannot. That is why Excel disables them by default, and that default is your friend. The rule is simple: run macros you wrote yourself, or copied from a source you trust and took a moment to read. The two macros in this guide only open text files you choose and write them into worksheets; you can verify that by reading the code, which is short enough to audit in a minute.

Never enable macros in a workbook that arrived unexpectedly by email or download, no matter how urgent the message claims to be — “enable macros to view this invoice” is one of the oldest malware lures on the internet. For your own macro workbooks, consider adding your working folder as a Trusted Location (Trust Center › Trusted Locations) so your own files open without the banner while everything else still prompts. Microsoft’s official documentation on importing data from CSV and text files is also worth bookmarking for the non-VBA side of the story.

That is the complete picture: you now know how to import CSV to Excel with VBA macro methods — from the single-file one-click version to the batch folder loop — with the delimiter, encoding, and security knowledge to adapt the code to your own files. Once your data is in Excel cleanly, the real work begins: our pivot table tutorial shows how to summarize thousands of imported rows in under a minute, and our guide to removing duplicates in Excel cleans up the repeated rows that CSV exports so often contain.