How to Read Multiple Excel Files and Merge Data into a Single File using VBA

Let us assume I have two Excel files (source files) with sales data in it. All the files (or workbook) have the same data structure. I want to extract all the data from the source files and merge the data to a single (master) file. See the image below.

The name of the source files can be anything. However, please make sure that all the files (except the master file) are in same folder.

The Master file, which will extract and merge data from all the files, should be in another folder. You can modify it further according to your requirement.

👉 Now, if you want to merge multiple Excel files into a single file but different sheets, you must read this post.

You need to write the Macro (or the code) in the Master file, as it will extract data from other files and write the data in it.

I have a Button on my master file. The button is an ActiveX control. The click event calls a functions to read all the files in a folder.

Option Explicit

Private Sub CommandButton1_Click()
    readAndMergeData
End Sub

Sub readAndMergeData()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    ' Our File System Objects.
    Dim objFs As Object
    Dim objFolder As Object
    Dim file As Object
    
    Set objFs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFs.GetFolder("D:\somefolder\sample")       ' The path for the files.
    
    ' The starting row. 
        ' We'll update this variable after it reaches the Last Row of the previous file.
    Dim iStartRow As Integer
    iStartRow = 0
    
    ' Loop through all the files in the folder.
    For Each file In objFolder.Files
        
        Dim src As Workbook         ' The source workbook.
        Set src = Workbooks.Open(file.Path, True, True)
        
        Dim iTotalRows As Integer   ' Get the total Used Range rows in the source file.
        iTotalRows = src.Worksheets("sheet1").UsedRange.Rows.Count
        
        Dim iTotalCols As Integer   ' Get the total Columns in the source file.
        iTotalCols = src.Worksheets("sheet1").UsedRange.Columns.Count

        Dim iRows, iCols As Integer
        
        ' Now, read the source and copy data to the master file.
        For iRows = 1 To iTotalRows
            For iCols = 1 To iTotalCols
                Cells(iRows + iStartRow, iCols) = src.Worksheets("Sheet1").Cells(iRows, iCols)
            Next iCols
        Next iRows
        
        iStartRow = iStartRow + iRows + 1
        iRows = 0
    
        src.Close False
        Set src = Nothing
    Next
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

In the above example, I am using VBA FSO (or File System Object) model for accessing files in a folder. The FSO provides object-based tools for working with files and folders. This is one of the easiest ways to get to know everything about the files in a folder.

I am creating an object of FSO.

Set objFs = CreateObject("Scripting.FileSystemObject")

Next, an object to read the folders

Set objFolder = objFs.GetFolder("D:\somefolder\sample")

👉 You can read more about FileSystemObject here.

As I have said before, the name of the files can be anything. However, the structure of each files must be same.

I’ll loop through each files in the folder. Using the File path, I am opening each workbook (the Excel file) one by one to read the data from it.

Set src = Workbooks.Open(file.Path, True, True)

Now, with the source file opened, I’ll get the total rows and columns from the file. Look at the UsedRange property. I don’t want to loop unnecessary through all the rows and columns.

iTotalRows = src.Worksheets("sheet1").UsedRange.Rows.Count
iTotalCols = src.Worksheets("sheet1").UsedRange.Columns.Count

That’s it. Now read each cell’s value in the source and write it to the Master file.

← PreviousNext →