Import Multiple Files Using VBA in Excel: A Practical Guide

Yes. In Excel for Microsoft 365 on Windows, VBA can loop through files in a selected folder, open each one, and copy its data into one combined worksheet or into separate sheets. The example below combines CSV files that share the same column layout; you can adapt the approach when you need to import other file types.

Choose how the files should be imported

Before writing a macro, decide what “import multiple files” means for your task. A folder of similarly structured CSV exports is a good fit for combining rows into one table. If each file represents a different report, copying each file to its own worksheet may be more useful. For Excel workbooks, you can also copy a specified sheet or range from each source workbook rather than importing a CSV.

Combine rows: Append the records from each file beneath one set of headers. This is useful for monthly exports, survey responses, or transaction lists.
Separate worksheets: Keep each file’s data on its own tab. This preserves file boundaries but can make analysis across all records harder.
Import selected ranges: Copy only a defined table or range if the source files contain titles, notes, or other material outside the data.

The sample macro uses the first option. It expects CSV files with headers in row 1, the same number and order of columns, and a populated first column in every data row. It copies values, not formulas, formatting, comments, or source workbook features. If the files have different layouts, first standardize them or add logic that maps each source column to the intended destination column.

Prepare the workbook and files

Put the CSV files to import in one folder. Keep unrelated CSVs out of that folder, because the macro processes every file matching the .csv extension. Create or open the destination workbook, add a worksheet named Combined, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). The macro clears existing cell contents on that sheet before importing, so do not store unrelated work there.

For a first run, make a copy of the destination workbook and test with a small folder containing two or three representative files. Check that each file has the same headers and column order. CSV files do not retain Excel formatting or multiple worksheets; they are plain-text, delimited data. If a file is actually an .xlsx workbook, this CSV-specific code will not import it just by changing the file extension.

VBA macro to combine CSV files

In desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in the code. When prompted, select the folder containing the CSV files. The first file supplies the headers; later files contribute rows starting at row 2, so their header rows are not repeated.

Option Explicit

Sub CombineCsvFiles()
    Dim picker As FileDialog
    Dim folderPath As String
    Dim fileName As String
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim firstDataRow As Long
    Dim nextRow As Long
    Dim expectedCols As Long
    Dim firstFile As Boolean
    Dim rowsToCopy As Long
    Dim sourceRange As Range
    Dim targetRange As Range

    On Error GoTo HandleError

    Set picker = Application.FileDialog(msoFileDialogFolderPicker)
    With picker
        .Title = "Select folder containing CSV files"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1)
    End With

    Set wsTarget = ThisWorkbook.Worksheets("Combined")
    wsTarget.Cells.ClearContents
    nextRow = 1
    firstFile = True
    fileName = Dir(folderPath & Application.PathSeparator & "*.csv")

    Do While Len(fileName) > 0
        Set wbSource = Workbooks.Open( _
            Filename:=folderPath & Application.PathSeparator & fileName, _
            ReadOnly:=True, Local:=True)
        Set wsSource = wbSource.Worksheets(1)

        ' Assumes column A is populated through the last data row.
        lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
        ' Assumes headers are in row 1.
        lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

        If firstFile Then
            expectedCols = lastCol
        ElseIf lastCol <> expectedCols Then
            Err.Raise vbObjectError + 1000, "CombineCsvFiles", _
                "Column count differs in " & fileName
        End If

        If firstFile Then
            firstDataRow = 1
        Else
            firstDataRow = 2
        End If

        If lastRow >= firstDataRow Then
            Set sourceRange = wsSource.Range( _
                wsSource.Cells(firstDataRow, 1), _
                wsSource.Cells(lastRow, lastCol))
            rowsToCopy = sourceRange.Rows.Count
            Set targetRange = wsTarget.Cells(nextRow, 1).Resize( _
                rowsToCopy, lastCol)
            targetRange.Value = sourceRange.Value
            nextRow = nextRow + rowsToCopy
        End If

        firstFile = False
        wbSource.Close SaveChanges:=False
        Set wbSource = Nothing
        fileName = Dir()
    Loop

    If firstFile Then
        MsgBox "No CSV files were found in the selected folder."
    Else
        MsgBox "Import complete: " & (nextRow - 1) & " rows written."
    End If
    Exit Sub

HandleError:
    If Not wbSource Is Nothing Then wbSource.Close SaveChanges:=False
    MsgBox "Import stopped: " & Err.Description, vbExclamation
End Sub

The macro checks that each file has the same number of columns as the first file, then stops with an error if the counts differ. It does not confirm that header names match, so a file with the same number of columns but different headings can still be appended incorrectly. Review the source headers when consistency matters, or extend the macro to compare them before copying.

Run the macro and check the result

Return to Excel, ensure the destination workbook is active, and run CombineCsvFiles from the Macro dialog or the Visual Basic Editor. On Windows, Alt+F8 opens the Macro dialog. Select the folder in the picker and wait for the completion message. Then inspect the Combined sheet: check the header row, a few records from the beginning and end, and the total row count against the source files.

The completion message counts rows written, including the first file’s header. For example, if the first CSV has one header and 100 records and a second CSV has one header and 40 records, the result contains 141 rows: one header plus 140 records. The macro opens each source read-only and closes it without saving changes. It copies values into the destination rather than using the clipboard, which avoids carrying over source formatting and makes the output a plain consolidated data range.

Adapt the method for other file types

For Excel workbooks, loop over a file pattern such as *.xlsx, open each workbook with Workbooks.Open, and copy a known worksheet or range. You will need to decide what happens if a source sheet is missing, a workbook is password-protected, or columns differ. Avoid copying the entire worksheet unless that is genuinely required; copying a defined range or table is easier to control.

To put each CSV on a separate destination tab, create a worksheet for each file and copy the source range to that sheet rather than appending to Combined. Worksheet names have limits and cannot contain certain characters, so filenames need to be cleaned and shortened before using them as tab names. Also handle duplicate names. A filename is not automatically a safe or unique worksheet name.

For large datasets or recurring imports, Excel’s Power Query is often a better alternative. In desktop Excel, use Data > Get Data > From File > From Folder, select the folder, and use the combine options to transform and append compatible files. Power Query records transformation steps and can refresh from the folder later; VBA is more flexible for custom workbook actions and automation, but requires macro-enabled files and code maintenance.

Common problems and their causes

Columns appear in the wrong places: Files may have different column orders, delimiter settings, or headers. Compare the originals and confirm the intended delimiter before importing.
Dates or numbers change: CSV text does not preserve Excel’s cell types. Excel may interpret date and decimal formats according to regional settings. Review the imported values, especially dates, leading zeros, and decimal numbers.
Only part of a file is copied: This example finds the last row by looking upward in column A. If a data row has a blank in column A, or the final records have blanks there, the detected range can end too early. Choose a reliably populated key column or use a more suitable method to find the data boundary.
One file stops the import: A different column count triggers an error and stops the loop. Inspect that file’s structure; do not silently append mismatched data unless you have explicitly mapped its fields.
Text is split or combined unexpectedly: CSV delimiter and quoting conventions can vary. A comma inside a properly quoted field should be treated as part of that field, but regional settings and nonstandard exports can affect how Excel opens text files. Test with the actual files and adjust the import method if necessary.

One common misconception is that a CSV extension guarantees a consistent layout. It does not: two files can use different delimiters, encodings, headers, or decimal conventions while both ending in .csv. Another is that Ctrl+Z will reverse a macro. VBA operations generally cannot be undone with Excel’s Undo command, which is why a backup and a test run are important.

Safety, compatibility, and alternatives

Only enable macros in workbooks and files you trust. The sample macro reads files from a folder chosen by the user and writes to the current workbook, but macros can perform broader actions. Save a separate copy before running or adapting it; macro changes cannot be undone with Ctrl+Z. After a successful import, save the destination as .xlsm if you need to retain the VBA code.

This example is written for Excel for Microsoft 365 on Windows. Excel for the web cannot run VBA macros. Excel for Mac supports VBA, but dialogs, file paths, and some automation behavior can differ; the folder-picker approach may need adjustment and should be tested on the Mac version in use. If portability or repeatable refresh matters more than custom VBA actions, Power Query is usually worth considering. For a one-time import, Excel’s built-in text import options may be enough.

Frequently Asked Questions

Can VBA import all files from a folder automatically?
Yes. VBA can use a folder picker and a file-search loop such as Dir to process files matching a pattern. Limit the folder contents carefully so unrelated matching files are not included.

Can I combine CSV files without repeating the headers?
Yes. Copy the header from the first file and start copying subsequent files at row 2, as the example does. This assumes every file has one header row with the same fields in the same order.

Can the macro import multiple Excel workbooks instead?
Yes, but the code must open workbook files and identify the source sheet or range to copy. The CSV example is not a general-purpose importer for .xlsx files.

Why does Excel change leading zeros or dates in a CSV?
CSV stores text fields, not Excel formatting or data types. When Excel opens a CSV, it may interpret values such as dates or numeric-looking IDs. If leading zeros matter, import the relevant column as text using an appropriate text-import method.

Will the macro work in Excel for the web or on a Mac?
Excel for the web does not run VBA. Excel for Mac supports VBA, but this Windows-focused folder-picker code may need changes for file selection and paths; test it in the target desktop version.

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.