How to Convert CSV to Excel Automatically with a VBA Macro

To convert CSV to Excel automatically with a VBA macro, write a macro that opens the CSV with Workbooks.Open (or Workbooks.OpenText for delimiter control), applies your formatting and cleaning steps, and saves the result as .xlsx. Run it once per file — or point it at a folder and let it convert every CSV inside. If you receive the same report every day or every week, a macro turns a ten-minute chore into a single click. (For one-off files, the free CSV to Excel converter is faster than writing any code.)

A macro is worth it when the import is repetitive: same file layout, same cleaning steps, same destination format, over and over. It is not worth it for a file you will open once.

Before You Start: Enable the Developer Tab

1. Right-click the ribbon and choose Customize the Ribbon.
2. Tick Developer in the right-hand list and click OK.
3. Open the Visual Basic editor with Developer > Visual Basic (or Alt+F11).

Save your macro workbook as .xlsm (macro-enabled). A plain .xlsx silently strips macros when you save.

Macro 1: One-Click CSV Import

This macro asks you to pick a CSV, imports it with explicit delimiter and encoding settings, and saves it as .xlsx next to the original:

Sub ImportCsvToExcel()
    Dim csvPath As Variant
    Dim wb As Workbook

    csvPath = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Select a CSV file")
    If csvPath = False Then Exit Sub

    ' OpenText gives delimiter control; adjust to match your file
    Workbooks.OpenText Filename:=csvPath, _
        Origin:=65001, _              ' 65001 = UTF-8
        DataType:=xlDelimited, _
        Comma:=True, _
        Tab:=False, Semicolon:=False, Space:=False

    Set wb = ActiveWorkbook

    ' Save as real .xlsx next to the source file
    wb.SaveAs Filename:=Left(csvPath, Len(csvPath) - 4) & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook
End Sub

Change Comma:=True to Semicolon:=True for European files, or set Tab:=True for TSV. The Origin:=65001 parameter sets UTF-8; use 1252 for Windows-1252 files. (If encoding problems persist, the guide to fixing CSV encoding errors in Excel explains the underlying causes.)

Macro 2: Import Plus Your Standard Cleaning

Most real imports need the same touch-ups every time: set column widths, freeze the header row, format dates, fix a code column. Record those steps once (Developer > Record Macro), then paste the recorded code into your import macro after the Workbooks.OpenText line. A typical recorded block looks like this after cleanup:

    ' --- standard cleaning (recorded once, replayed every run) ---
    With wb.Worksheets(1)
        .Rows(1).Font.Bold = True
        .Rows("1:1").AutoFilter
        .Columns("A:Z").AutoFit
        .Columns("A:A").NumberFormat = "@"   ' text: keeps leading zeros
        .Range("A2").Select
        ActiveWindow.FreezePanes = True
    End With

Keep the recorded code tidy: the recorder captures every scroll and selection, so delete the .Select lines you do not need and keep only the formatting statements. For date columns that arrive as text, the CSV date-format fix guide shows the conversion patterns to bake into the macro.

Macro 3: Batch-Convert a Whole Folder

When a folder fills up with CSVs, loop over it:

Sub BatchConvertCsvFolder()
    Dim folder As String, f As String
    Dim wb As Workbook

    folder = "C:\Reports\Incoming\"
    f = Dir(folder & "*.csv")

    Application.ScreenUpdating = False
    Do While f <> ""
        Workbooks.Open Filename:=folder & f, Local:=True
        Set wb = ActiveWorkbook
        wb.SaveAs Filename:=folder & Left(f, Len(f) - 4) & ".xlsx", _
            FileFormat:=xlOpenXMLWorkbook
        wb.Close SaveChanges:=False
        f = Dir()
    Loop
    Application.ScreenUpdating = True

    MsgBox "Done — every CSV in the folder is now an .xlsx."
End Sub

Application.ScreenUpdating = False keeps the loop fast and flicker-free. Local:=True makes Excel respect your regional delimiter settings. Test the loop on copies first — a macro that saves over originals cannot be undone with Ctrl+Z.

Macro vs Power Query: Which One?

If the cleaning is complex (merging, pivoting, type surgery), Power Query's recorded steps are easier to maintain than VBA. The guide to combining multiple CSV files with Power Query shows the alternative approach. For simple "open, tidy, save" repetition, a macro is less machinery.

Safety Rules for Import Macros

• Work on copies. A macro that opens, converts, and closes files can overwrite originals in seconds. Point it at a copy of the folder until the logic is proven.
• Turn off alerts deliberately. Application.DisplayAlerts = False suppresses the "file already exists" prompt — useful in a loop, dangerous while testing. Leave alerts on until the macro is final.
• Handle errors. Wrap the loop body in basic error handling so one corrupt CSV does not abort the whole batch:

“`vba On Error Resume Next ' … import and save … If Err.Number <> 0 Then Debug.Print "Failed: " & f On Error GoTo 0 “`

• Keep the .xlsm. The macro lives in the workbook that contains it. Store that workbook somewhere safe and back it up — losing it means rewriting the automation.
• Macro security. Recipients of your .xlsm will see a security warning. That is normal and correct; only enable macros from sources you trust.

FAQ

Do I need to know programming to automate a CSV import?

Not much. Record your steps with Developer > Record Macro, then read the generated code — it is verbose but readable. The three macros above cover most real needs with only the folder path and delimiter to adjust.

Will a macro work on a Mac?

Partially. Excel for Mac supports VBA but with limitations (some file-system calls and Windows-specific features differ). Test Mac macros separately; Power Query on Mac is also limited, so complex automation is genuinely harder there.

Can a macro fix encoding problems automatically?

It can set the encoding (Origin:=65001 for UTF-8), which prevents most problems at import time. It cannot repair a file whose bytes are already misread — that has to be fixed by importing with the right origin, as the encoding guide explains.

How do I run the macro automatically when a file arrives?

Use Windows Task Scheduler to open the .xlsm at a set time, with a Workbook_Open event that runs the import. That is a scheduled job, not a macro feature — the macro does the converting, the scheduler does the timing.

Is VBA safe to use?

Yes, when you write or vet the code yourself. Never enable macros in a workbook from an untrusted source — macros can do anything you can do in Excel, including deleting files. Keep your automation workbooks separate from files you receive from others.

Final Checklist

• Developer tab enabled; macro workbook saved as .xlsm.
• Import macro tested on a copy with the right delimiter and encoding.
• Cleaning steps recorded once and pasted into the macro; recorded clutter removed.
• Batch loop tested on copies; ScreenUpdating off; error handling in place.
• Originals untouched — outputs saved as new .xlsx files.

If all five checks pass, your CSV import now runs itself — one click today, one click every day after.