Help Modifying Ron de Bruin's MergeAllWorkbooks Subroutine

[RocketDude]

I need help modifying Ron de Bruin's MergeAllWorkbooks subroutine
(http://msdn.microsoft.com/en-us/library/cc837974.aspx) so that it will
consolidate all workbooks with a specific name, but each workbook will be in
a different sub-folder.

So my files look like this:

Main Directory
Sub-Folder1
File.xls
Sub-Folder2
File.xls
.....
Sub-FolderXX
File.xls

So I want to modify Ron's routine so it crawls all the sub-folders, and
consolidates all of the File.xls files into a single workbook. The added
complexity is that some sub-folders may not have a "File.xls" in them, so
the routine needs to be able to handle this.

Thanks

 

[Ron de Bruin]

Hi RocketDude

You can start here
http://www.rondebruin.nl/fso.htm
Download the example workbook

Use this after you change the path in the RDB_Merge_Data macro in the
Get_Data_Macro module

myCountOfFiles = Get_File_Names( _
MyPath:="C:\Users\Ron\test", _
Subfolders:=True, _
ExtStr:=" File.xls", _
myReturnedFiles:=myFiles)

 

[Ron de Bruin]

Oops i see a space in the code

Use this

myCountOfFiles = Get_File_Names( _
MyPath:="C:\Users\Ron\test", _
Subfolders:=True, _
ExtStr:="File.xls", _
myReturnedFiles:=myFiles)

 

[Ron de Bruin]

Hi RocketDude

You can also use the Add-in (easier )
http://www.rondebruin.nl/merge.htm

 

[RocketDude]

Ron,

Thank you.

Follow-up question:

Once I consolidate all of the Excel files into one, I need to convert it to
row data that I will then import into Access.

Data Collection Format: -- There may or may not be data in columns C3 - C8

C1 C2 C3 C4 C5 C6 C7 C8
--------------------------------------------------------
D1 D2 D3 D4 D5 D6 D7 D8
D9 D10 D11 D12


Wanted Data Format:

C1' C2' C3'
----------------------
D1 D2 D4
....
D1 D2 D8
D9 D10 D11
D9 D10 D12

The additional complications is that I only want to create a row for columns
C3 - C8 if that cell contains data.

Do you have any sample code (or can you point me in the direction to get
started) to accomplish this?
I'm expecting to have about >2000 rows initially, so manually doing this is
out of the question.

Thanks,

RocketDude