Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business
Are you a data enthusiast? Join us for a ride on your data and automate your stuffs

VBA Tool to Merge Excel Files from a Folder

Merge Excel Files with one click Excel VBA

From last few months, we have been receiving frequent requests from users for a VBA tool which can consolidate Excel files from a folder. So here we come with one more free VBA tool from ExcelSirJi team. With this Excel Consolidator tool, you will be able to consolidate data from all Excel files in a folder.


Merge Multiple Excel Files into one Excel File

Please note that:

  • Tool will only consolidate active sheet of each file
  • All files should have same column header for accurate consolidation
  • Tool supports consolidation of .xls, .xlsx and .xlsm files
  • Row one of each file is considered as Header row
'This function loops through all Excel files available in the folder
'and consolidates the data of active sheet
Public Sub ConsolidateFiles()
    '
    Dim objFileSys As Object
    Dim objFiles As Object
    Dim objFile As Object
    Dim strExtn As String
    Dim bIsHeaderAdded As Boolean
    Dim wkbFile As Workbook
    Dim wksSheet As Worksheet
    Dim lConsolCounter As Long
    '
    'Validate
    If Trim(wksConsol.Range("C3").Value) = "" Then
        MsgBox "Please select a folder to proceed", vbInformation
        Exit Sub
    End If
    '
    'Clear old data
    wksConsol.Range("5:" & wksConsol.Cells.SpecialCells(xlCellTypeLastCell).Row + 10).Delete
    '
    On Error GoTo Error_Import
    '
    Set objFileSys = CreateObject("Scripting.FileSystemobject")
    Set objFiles = objFileSys.GetFolder(wksConsol.Range("C3").Value & "\")
    '
    bIsHeaderAdded = False
    lConsolCounter = 6
    For Each objFile In objFiles.Files
        strExtn = LCase(Right(objFile.Path, InStr(1, StrReverse(objFile.Path), ".")))
        If strExtn = ".xls" Or strExtn = ".xlsx" Or strExtn = ".xlsm" Then
            'Open the file
            Set wkbFile = Workbooks.Open(objFile.Path, False, True)
            Set wksSheet = wkbFile.ActiveSheet
            '
            'Copy header
            If bIsHeaderAdded = False Then
                wksSheet.Range("1:1").Copy wksConsol.Range("A5")
                bIsHeaderAdded = True
            End If
            '
            'Copy content
            If wksSheet.Cells.SpecialCells(xlCellTypeLastCell).Row > 1 Then
                wksSheet.Range("2:" & wksSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Copy wksConsol.Range("A" & lConsolCounter)
                lConsolCounter = lConsolCounter + wksSheet.Cells.SpecialCells(xlCellTypeLastCell).Row - 1
            End If
            '
            'Close the file
            wkbFile.Close False
        End If
    Next
    '
    MsgBox "Done", vbInformation
    '
    Exit Sub
    '
Error_Import:
    MsgBox "Unable to consolidate files" & vbNewLine & vbNewLine & "Error: " & Err.Description, vbCritical
    Exit Sub
    '
End Sub

How to use this tool:

  • Save the following attachment in your system Excel Consolidator Tool
  • Unzip the file and open
  • Browse the folder which has the Excel files you want to consolidate
  • Download the tool from here:
  • Click on ‘Consolidate’ button
  • Done, tool will consolidate data from each file and show the confirmation once completed

Here we finish this article. Please share your feedback and we are sure that you liked this article.

Keep Reading us 🙂

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

Your email address will not be published. Required fields are marked *