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 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
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
Great information brothers
Thank you mate 🙂