Merge Excel Files

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 Excel Files

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
Merge Excel Files
  • 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 🙂

Recommended Articles

Excel VBA Course : Beginners to Advanced

We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion

This course is going to help you to excel your skills in Excel VBA with our real time case studies.

Lets get connected and start learning now. Click here to Enroll.

Secrets of Excel Data Visualization: Beginners to Advanced Course

Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.

This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.

So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.

Secrets of Excel Data Visualization: Beginners to Advanced Course

Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.

This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.

So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.

Similar Posts

3 Comments

  1. excelsirji, the code seems to work fine, however I’m getting this error at the end of the macro: Unable to consolidate files Error: Excel cannot open this file ‘~$20201211.xlsx’ because the file format or file extension is not valid……
    When in fact it does consolidate the files properly and there is no file with that name. I’m guessing it is trying to open the ‘temp file’ that is created each time a file is opened, but I’m not sure how to resolve the error. Don

Leave a Reply

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