Help Modifying Ron de Bruin's MergeAllWorkbooks Subroutine
 

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

Help Modifying Ron de Bruin's MergeAllWorkbooks Subroutine
 

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)




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"RocketDude" wrote in message ...
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

Help Modifying Ron de Bruin's MergeAllWorkbooks Subroutine
 

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)



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
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)




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"RocketDude" wrote in message ...
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

Help Modifying Ron de Bruin's MergeAllWorkbooks Subroutine
 

Hi RocketDude

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"RocketDude" wrote in message ...
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

Help Modifying Ron de Bruin's MergeAllWorkbooks Subroutine
 

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


"Ron de Bruin" wrote in message
...
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)



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
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)




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"RocketDude" wrote in message
...
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