Merge multiple excel files in multiple folders using vba macros

Question

Need to loop through multiple folders (3 folders in same directory) and merge all files from these multiple folders.

The below code consolidates all files from a single folder (A). I need to consolidate all files from 3 different folders: A,B and C.

My question is: how do I add a loop to consolidate files from the remaining 2 folders

This is the excel-vba code:

Sub Merge()
With Worksheets("Sheet2")
Dim bookList As Workbook
Dim MergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Dim sCURFolderPath As String
Dim sHostFolder As String
Application.ScreenUpdating = False

Set MergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
sCURFolderPath = Application.ActiveWorkbook.Path       ' Current directory folder
sHostFolder = sCURFolderPath & "\First Month"                 

Set dirObj = MergeObj.GetFolder(sHostFolder)
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

Range("A2:Z" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Sheet2").Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next

End With
End Sub

What is your question? What version of Excel are you using? Why A65536? Are you still on 2003 or earlier? — teylyn
@teylyn: I'm using Excel 2013. The above code consolidates all records from multiple files from a single folder (A). I need to consolidate all files from 3 different folders: A,B and C. My question is: how do I add a loop to consolidate files from the remaining 2 folders. — Notafanofcoding
With Excel 2013 you can use Power Query instead of VBA. It's a lot cleaner and all you need to do is click a few icons. Use From Folder. — teylyn
@teylyn: I did explore the Power Query Option. :) But I'm looking to merge about 60 .xls files from 3 folders and hence I decided to go with VBA Macro. — Notafanofcoding

scott scott · Accepted Answer · 2017-09-14T23:24:40

The secret lies in changing the value of sHostFolder which is currently equal to: sCURFolderPath & "\First Month"

There are two scenarios here: either you (1) know the qty and names of the subfolders or (2) the qty and names of subfolders are unknown

scenario (1): instead of a loop you would place your above code into a function with a single string parameter containing the folder name, and then call the function three times:

MyFunction("FirstMonth")
MyFunction("SecondMonth")
etc...

scenario (2): Once you know the name of a directory, you can make repeated calls to the filesystem Dir object:

Dir(sitePath, vbDirectory)

your loop can check the length/value returned each time and if there is a subdirectory name then you can use this value as part of your sHostFolder variable and then run your existing code. obviously, you would exit the loop after the Dir() fails to return a valid subdirectory name.

Best of luck!

Merge multiple excel files in multiple folders using vba macros

2 Answers