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.
Please note that:
'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:
Â
Â
Here we finish this article. Please share your feedback and we are sure that you liked this article.
Keep Reading usÂ
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.
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.
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.
This Excel VBA Code helps to Get User Name. Here is an example environ(username) or Application.username.This macro gets the username from active directory.
VBA code that will sum cells by its color through excel function. This code will really help in making the analysis and presentation better.
How to Add Outlook Reference in Excel VBA? To automate Outlook based tasks from Excel you need to add Outlook Object Library (Microsoft Outlook XX.X Object Library) in Excel References. You can follow below steps…
VBA Code to Count Color Cells With Conditional Formatting Have you ever got into situation in office where you need to count the cells with specific color in conditional formatted Excel sheet? If yes then…
Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes.
Time Management is very effective way of managing the available time. Current competitive world is making the time management crucial. There are various ways, tools and techniques by which time management can be done easily.
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 🙂