[Excel VBA] How to Aggregate Data from Multiple Closed Excel Files

Aggregating data from multiple Excel files is a common business task, but manually opening, copying, and pasting each file is extremely time-consuming.

This article explains how to create a VBA macro that automatically opens closed Excel workbooks and compiles their data into a single master sheet.

Scenario

• Main File: We will use a file named SummaryMacro.xlsm.
• Source Files: sourceA.xlsx, sourceB.xlsx, and sourceC.xlsx are stored in an import folder located in the same directory as the main file.
• Data Structure: The first row of each source file contains headers. We want to extract data starting from the second row.
• Target: We will read data from the first sheet (Sheet1) of each source file and append it to the bottom of the first sheet (Sheet1) of the main file.

VBA Code Example

Sub ImportDataFromClosedBooks()
    Dim targetSheet As Worksheet
    Dim importBook As Workbook
    Dim fileList As Variant
    Dim fileName As Variant
    Dim folderPath As String
    
    ' Set the destination sheet (Sheet1 of this macro workbook)
    Set targetSheet = ThisWorkbook.Worksheets(1)
    
    ' Define the folder path containing the source files
    ' "\import\" assumes the folder is in the same directory as this workbook
    folderPath = ThisWorkbook.Path & "\import\"
    
    ' List of files to process
    fileList = Array("sourceA.xlsx", "sourceB.xlsx", "sourceC.xlsx")
    
    ' Loop through each file in the list
    For Each fileName In fileList
        ' Open the workbook temporarily
        Set importBook = Workbooks.Open(folderPath & fileName)
        
        With importBook.Worksheets(1)
            Dim dataBody As Range
            
            ' Determine the data area (CurrentRegion includes headers)
            Set dataBody = .Range("A1").CurrentRegion
            
            ' Exclude the header row (Offset by 1 row, reduce size by 1 row)
            Set dataBody = dataBody.Offset(1).Resize(dataBody.Rows.Count - 1)
            
            ' Copy data to the next available row in the target sheet
            dataBody.Copy targetSheet.Cells(targetSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
        End With
        
        ' Close the source workbook without saving changes
        importBook.Close SaveChanges:=False
    Next
End Sub

Key Points

• Workbooks.Open: This command is used to temporarily open the closed files so data can be read.
• .Offset(1).Resize(...): This logic excludes the header row (Row 1). It shifts the selection down by one row and reduces the total row count by one to capture only the data body.
• Cells(Rows.Count, 1).End(xlUp).Row + 1: This calculates the first empty row at the bottom of the target sheet, ensuring new data is appended rather than overwritten.
• SaveChanges:=False: This closes the external file without saving, ensuring the original source files remain unaltered.

Important Notes

1. Folder Location: The import folder must exist in the exact same location as your macro file (SummaryMacro.xlsm).
2. Consistent Layout: All source files must have the same column layout.
3. File Locking: Ensure the source files are not currently open by other users to avoid errors.

Summary

By using VBA, you can efficiently collect data from multiple closed Excel workbooks. This technique is highly useful for automating data consolidation tasks and reducing manual errors.