[Excel VBA] Macro to Automatically Import Data from All Excel Files in a Folder

There is a very common business need to batch read data from multiple Excel files stored in a specific folder. In this article, I will introduce a VBA macro that automatically aggregates data from all Excel workbooks in a specified folder.

Scenario Overview

• Prepare a file named “DataCollector.xlsm” to run the macro.
• Create a “datafiles” folder in the same directory and store multiple Excel files (.xlsx) inside it.
• The first row of each file is the header, and the data starts from the second row.
• The read data is added to the end of the first sheet of “DataCollector.xlsm”.

VBA Code: Import Data from All Excel Files at Once

Sub CollectDataFromAllFiles()
    Dim wsTarget As Worksheet
    Dim rngSource As Range
    Dim wbSource As Workbook
    Dim folderPath As String
    Dim fileName As String
    
    Set wsTarget = ThisWorkbook.Worksheets(1)
    folderPath = ThisWorkbook.Path & "\datafiles\"
    
    fileName = Dir(folderPath & "*.xlsx")
    
    Do While fileName <> ""
        Set wbSource = Workbooks.Open(folderPath & fileName)
        
        With wbSource.Worksheets(1)
            Set rngSource = .Range("A1").CurrentRegion
            Set rngSource = rngSource.Offset(1).Resize(rngSource.Rows.Count - 1)
            rngSource.Copy wsTarget.Cells(wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row + 1, 1)
        End With
        
        wbSource.Close SaveChanges:=False
        fileName = Dir
    Loop
End Sub

Explanation

1. Scan Files: The Dir function is used to scan all .xlsx files in the target folder.
2. Copy Data: Each workbook is opened temporarily. The code copies only the data portion, excluding the first row (header).
3. Paste: The destination is set to immediately follow the last row of the target workbook.
4. Close: Finally, the source workbook is closed without saving.

Usage Notes

• Structure: It assumes that all Excel files have the same structure (headers start at A1 and the column configuration is identical).
• File Type: By default, .xlsm and .xls files are not read. If necessary, change the code to Dir(folderPath & "*.xls*").
• Header: If the destination sheet is empty, it is safer to manually prepare a “header row” before running the macro.

Application Examples

• Change the logic based on the file name.
• Extract only specific columns.
• Add data validation (e.g., removing duplicates or checking input rules).

Summary

Using this macro can significantly improve efficiency by automating the task of importing data without opening files one by one. Please use this to automate your routine tasks.