VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One

by

Download Link : Consolidator_1.0

If you want to combine many excel files into one file and you don’t want to do it manually, you have come to the right place. This piece of software will allow you merge as many excel files you want, say 500-1000 excel files. This tool will provide you various options of formatting before you merge your files.

How to Use it:

  1. Download the consolidator.xlsm from the link provided at the top and at the bottom of this article.
  2. Place all the excel files, which you want to combine, into one folder (make sure all files are closed).

Excel Files
Excel Files

     3. Open the Consolidator.xlsm.

     4. Go to the sheet 2(“Change Path and header settings”). In this sheet you have options to-

  • “Remove blank rows” if available in any of this files while combining.
  • “Remove repeated headers” if available in any of this files while combining.
  •  Provide the folder path where you all excel files are stored.

   5. Once done with setting , you click the “Click to combine files” either from sheet 1(“List of Files“) or Sheet 2 (“Change Path and header settings”)

   6. That’s it, your all the files from the specified folder will be combined into one and gets stored in to Sheet 3(“Combine”). At the end you will get a message box stating how many files are combined and name of all the files combined in sheet 1.

Files Output
Files Output

7. Output with

  • Remove blank rows – False
  • Remove repeated headers – False
Output - with Repeated Header and blank Rows
Merged File- with Repeated Header and blank Rows

 

8. Output with

  • Remove blank rows – True
  • Remove repeated headers – True
Merged File- with one Header and deleted blank Rows
Merged File- with one Header and deleted blank Rows

Complete Code:
[sourcecode language=”VB”]
Sub sumit()

Dim fso As New FileSystemObject

Dim NoOfFiles As Double
Dim counter As Integer
Dim r_counter As Integer
Dim s As String
Dim listfiles As Files
Dim newfile As Worksheet
Dim mainworkbook As Workbook
Dim combinedworksheet As Worksheet
Dim tempworkbook As Workbook
Dim rowcounter As Double
Dim rowpasted As Integer
Dim delHeaderRow As Integer
Dim Folderpath As Variant
Dim headerset As Variant
Dim Actualrowcount As Double
Dim x As Long
Dim Delete_Remove_Blank_Rows As String

Folderpath = ActiveWorkbook.Sheets(2).Range("I7").Value
headerset = ActiveWorkbook.Sheets(2).Range("F4").Value
Delete_Remove_Blank_Rows = ActiveWorkbook.Sheets(2).Range("F3").Value

NoOfFiles = fso.GetFolder(Folderpath).Files.Count
Dim Files_Count_No_Of_Rows_In_Sheets(1000) As Double ‘declare the array of the size of no of files in the folder

Set listfiles = fso.GetFolder(Folderpath).Files
counter = 0
r_counter = 1
rowcounter = 1
Actualrowcount = 0

For Each fls In listfiles
counter = counter + 1
Range("A" & counter).Value = fls.Name
Next
‘MsgBox ("count of files in folder is " & NoOfFiles)
Set mainworkbook = ActiveWorkbook
Set combinedworksheet = mainworkbook.Sheets(2)
mainworkbook.Sheets(3).UsedRange.Clear
‘MsgBox ("Sheet is clear for the data to be copied")
For i = 1 To NoOfFiles
mainworkbook.Sheets("Combine").Activate
‘MsgBox ("Sheet 3 is Activated")
mainworkbook.Sheets("Combine").Range("A" & rowcounter).Select
Application.Workbooks.Open (Folderpath & "\" & Range("A" & i).Value)
Set tempworkbook = ActiveWorkbook
Set newfile = ActiveSheet
rowpasted = rowcounter
‘MsgBox ("pointer at " & rowpasted)
newfile.UsedRange.Copy
‘MsgBox ("Data is copied")
mainworkbook.Sheets(3).Paste
‘MsgBox ("Data is pasted successfully")
‘MsgBox ("Blank rows has been deleted " & Remove_Blank_Rows & " " & headerset)
If Delete_Remove_Blank_Rows = "Yes" Then
‘If Remove_Blank_Rows = Yes Then
‘MsgBox ("Blank rows has been deleted" & Delete_Remove_Blank_Rows)
For x = mainworkbook.Sheets("Combine").Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(mainworkbook.Sheets("Combine").Rows(x)) = 0 Then
mainworkbook.Sheets("Combine").Rows(x).Delete
‘MsgBox ("Blank rows has been deleted" & Remove_Blank_Rows)
End If
Next
End If
rowcounter = mainworkbook.Sheets(3).UsedRange.Rows.Count + 1
‘MsgBox ("row counter is updated" & rowcounter)
rowpasted = rowcounter – rowpasted
‘MsgBox ("No fo rows pasted" & rowpasted)
delHeaderRow = rowcounter – rowpasted
‘MsgBox ("Which row to delete" & delHeaderRow)
‘MsgBox ("Pointer at row beforw deletion" & rowpasted)
If headerset = "Yes" Or headerset = "YES" Or headerset = "yes" Then
If delHeaderRow <> 1 Then
mainworkbook.Sheets(3).Rows(delHeaderRow).EntireRow.Delete
rowcounter = rowcounter – 1
rowpasted = rowpasted – 1
Else
End If
Else
End If
‘MsgBox ("Header deleted")
‘MsgBox ("row counter is updated" & rowcounter)
combinedworksheet.UsedRange.ClearOutline
‘combinedworksheet.
tempworkbook.Close
‘MsgBox ("no of rows are abt to get pasted in sheet 1")

Files_Count_No_Of_Rows_In_Sheets(i) = rowpasted
Actualrowcount = Actualrowcount + rowpasted
Next i
mainworkbook.Sheets(1).UsedRange.ClearContents
For Each fls In listfiles
r_counter = r_counter + 1
mainworkbook.Sheets(1).Range("A" & r_counter).Value = fls.Name
mainworkbook.Sheets(1).Range("B" & r_counter).Value = Files_Count_No_Of_Rows_In_Sheets(r_counter – 1)
mainworkbook.Sheets(1).Range("A" & r_counter, "B" & r_counter).Borders.Value = 1

Next
mainworkbook.Sheets(1).Range("B" & r_counter + 1).Interior.ColorIndex = 46
mainworkbook.Sheets(1).Range("B" & r_counter + 1).Value = Actualrowcount
mainworkbook.Sheets(1).Range("B" & r_counter + 1).Borders.Value = 1
mainworkbook.Sheets(1).Range("A1", "B1").Interior.ColorIndex = 46
mainworkbook.Sheets(1).Range("A1", "B1").Borders.Value = 1
mainworkbook.Sheets(1).Range("A1").Value = "Files List"
mainworkbook.Sheets(1).Range("B1").Value = "No Of Rows"

MsgBox ("List of Files are Availabe in sheet 1..Total " & NoOfFiles & " Files Combiled")
End Sub

[/sourcecode]
Download Link : Consolidator_1.0

 

 

72 thoughts on “VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One”

  1. This website looks great. It seems this website fulfill all my needs. Step by steps codes giving good understanding to the users. As a learner I would rate 9 out of 10. If you share the password for the attached file then I would rate 10 out of 10. Once again thanks.

    Reply

  2. Thank you for sharing this great utility. I’ve used it to combine many individual txt files into one. One problem I discovered that i have is that, sometimes I get a 12 digit number. After the files are combined this field looses the original data and is replaced with a truncated entry (123450000000)).

    Currently I look for these 12 digit entries and add them manually.

    Are than any changes that I could make to the global variables that might accomodiate these numbers?

    Thank you.

    Reply

  3. I have 3 different excel file in which header are same ,but order of headers are different in 3 excel files ,Example in one excel file header name is PHF No. which is in Column A ,the same header in 2 excel file which is column D,the same header in 3 excel file which is column F,

    my QUERY is While merging it is not taking due to different columns having same headers

    Can you help me out in merging the 3 excel which are same in header but there presence in different coulmns ,and at the same time while merging them duplicate should be deleted automatically

    Pls help me and if possible pls send sample excel of it

    Reply

  4. Excellent work buddy. while working on my project, bumped into your tool. so much inline with what i need. possible for you to share the password of this VBA code component ? need to customize few things . you can send it to my email if sharing here is not what you like to do.

    Cheers
    PS

    Reply

  5. Thank you for this SJ, it’s proving to be extremely useful for me! Is it possible to make the combine include files within sub-folders?
    Cheers
    Jonny

    Reply

  6. Hello SJ! Your work is amazing but i just need to edit some things to fit my needs, is it ok if you also share the password with me? Thanks in advance!

    Reply

  7. Great tool. Exactly what I’ve looked for.
    I have 2 questions:
    1. In my workbook I have two headers rows. Is it possible to remove both of them when more then 1 file?
    2. Can you please share the password with me? I would like to edit few things.

    Thanks in advance,

    Reply

  8. Hi, it is great tool. I have one question:
    My each excel file has two sheets: sheet1 ,sheet2, how to combine them into one new excel file but keep all sheet1 in new excel sheet1 and sheet2 in new excel sheet2? Is it clear? Thanks in advance!

    Reply

    • Thanks for appreciation,
      You need to make few changes in the code.
      1. copy the data from sheet1 and put it in sheet1 of new file, existing code will do that for you.
      2. for sheet2, before you copy the data, activate the sheet2 ex: Workbook.Sheets(“Sheet2”).UsedRange.Copy and before you paste the data, activate the sheet2 in the destination excel file.(I do not think that you need to activate the sheet2 in destination excel file every time.)
      3. Existing code doesn’t not put the combined data in new file, so you can run the code twice for sheet1 and sheet2 after making changes mentioned in first 2 steps and manually copy the combined data into new file
      do let me know if you need further help.

      Reply

      • Thanks for your reply. I am not very clear about the second step. I have over 50 excle files,each has sheet2. How to activate them? I appreciate your help.

        Reply

        • it should be mainWorkBook.Sheets(“Sheet2”).Activate OR try copying directly from sheet2 mainWorkBook.Sheets(“Sheet2”).UsedRange.Copy
          if you still face problem, let me know, currently busy but i will try to provide the solution on weekend.

          Reply

  16. I am getting an Overflow error when consolidating. I need to combine 10 files with about 20,000 rows on each file. Is this something that can be fixed on the code? I hope you can share with me the password.

    Reply

  22. Hi, great tools. but for the blank row could it be included those formula blank row ?
    I mean Formula on the cell, but the result was blank. possible excluded as well?

    Reply

  25. I am get­ting an Over­flow error when con­sol­i­dat­ing. I need to com­bine 100 files with about 3000 rows on each file. Is this some­thing that can be fixed on the code? I hope you can share with me the password

    Reply

  26. HI can you provide me the password please. “overflow” error message when I tried to consolidate 15 files or higher. Thanks

    Reply

  28. Hi, this is exactly what I needed. I run into some problems while using the code provided in this post (specifically with removing the headers part which i’m unable to fix on my own). Is it possible to send me file password? [email protected] thanks.

    Reply

  32. Hello, one more thanking you for your efforts, could you provide pss to do modifications needed? Thanks in Advance, BR

    Reply

  33. Hey Great Stuff SJ !!! You really saved me a lot of time, i was working on the same lines since the last two days but was encountering some issues with my code needed to consolidate like 100 .csv files, this thing is just what I was looking for !!!!

    Could you provide me the password i’d like to make a few changes to adapt it to work? 🙂 Thanks in Advance, Sagar

    Reply

  34. Hi SJ,

    Thanks for the wonderful work you have done. However I am receiving Overflow Error message could you please mail me the password at [email protected], so that I can see if there is a solution to resolve this issue.

    Thanks & Regards,
    Sagar Kochar

    Reply

  35. Hi SJ,

    This tool is very useful. It helped me a lot in my projects. Im still making some format modifications in the combined files though. But overall, this is really a time saver. Could you please send me the pw, i would like to make a few modifications to address some of the format issues. Thanks in advance.

    Reply

  38. Hi
    Using this very useful code but I need to modify it. Can you please send me the password as many others have also requested. Just curious, why protect with a password as so many posts here are requesting it? Please do not publish my email address.
    Many thanks in advance

    Reply

Leave a Reply to AT Cancel reply