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

I have previously shared a post with an example on how to read data from a closed workbook without opening it. I am extending that example, and here in this post I’ll show you how to read multiple excel files or workbooks and merge the data into a single Excel 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.

Consolidate Multiple Excel files into One using VBA

Merge Multiple Files Data into a Single File in VBA

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 another folder. You can modify it further according to your requirement.

The VBA Code (Macro)

You need to write the Macro (or the code) in the Master file, as it extract the 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 the all the files in a folder.

Option Explicit

Private Sub CommandButton1_Click()
    ReadAndMerceData
End Sub

Sub ReadAndMerceData()
    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 = iRows + 1
        iRows = 0
    
        src.Close False
        Set src = Nothing
    Next
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

In the example above, I am using VBA FSO (or File System Object) model for accessing files in a folder. The FSO provides an object-based tool 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")

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, I have the source file, 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 to the Master file.

Thanks for reading.

Related Posts:

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Join our Google Plus Community and be a part of a discussion!