How do I combine multiple workbooks in Excel using a Macro?

I don't know what you mean as "combine", but I use this code to cycle through
all the Excel workbooks on the same directory:

Dim fso, f, fs, f1
Set fso = CreateObject("Scripting.FileSystemObject")
'This can be changed to reflect a different path
Set f = fso.getfolder(ActiveWorkbook.Path)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = "xls" Then
'Here you do whatever you need with the workbook
ProcessWorkbook f1.Name
End If
Next


--
It is nice to be important, but it is more important to be nice


"Miss Atlanta" wrote:

How do I combine multiple workbooks in Excel using a Macro?

See sample code at Ron de Bruin's site:

http://www.rondebruin.nl/copy3.htm

--
Regards,
Tom Ogilvy


"Miss Atlanta" <Miss wrote in message
...
How do I combine multiple workbooks in Excel using a Macro?

Thanks! Here is my situation:
I need to write a macro that will pull data(excel files) from different
folders in the same directory onto a "master" workbook that results in the
compiling of each file in the folders on to one sheet. Is that possible? My
Manager is needing this like Yesterday! (Please help again if you can)

"Miguel Zapico" wrote:

I don't know what you mean as "combine", but I use this code to cycle through
all the Excel workbooks on the same directory:

Dim fso, f, fs, f1
Set fso = CreateObject("Scripting.FileSystemObject")
'This can be changed to reflect a different path
Set f = fso.getfolder(ActiveWorkbook.Path)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = "xls" Then
'Here you do whatever you need with the workbook
ProcessWorkbook f1.Name
End If
Next


--
It is nice to be important, but it is more important to be nice


"Miss Atlanta" wrote:

How do I combine multiple workbooks in Excel using a Macro?

It can be done, here is some code that will trigger the data gathering for
all the files found in a directory and its subfolders:

OpenFiles "C:\whatever directory"
sub OpenFiles(folderspec)
Dim fso, f, f1, s, sf
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(folderspec)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = "xls" Then
CopyData folderspec & "\" & f1.name
End If
Next
Set sf = f.SubFolders
For Each f1 in sf
main folderspec & "\" & f1.name
Next
end Sub

You have to create the Sub CopyData with your special data needs, but
usually you have to do four things:
1.- Open the file that comes as a parameter to the subroutine
2.- Copy the information from the file, using whatever method is more
appropiate
3.- Paste the information in your consolidation file
4.- Close the file

This sub is too dependant of the type of data and the organization of the
files that I cannot give any valid example.

I hope this helps.

--
It is nice to be important, but it is more important to be nice


"Miss Atlanta" wrote:

Thanks! Here is my situation:
I need to write a macro that will pull data(excel files) from different
folders in the same directory onto a "master" workbook that results in the
compiling of each file in the folders on to one sheet. Is that possible? My
Manager is needing this like Yesterday! (Please help again if you can)

"Miguel Zapico" wrote:

I don't know what you mean as "combine", but I use this code to cycle through
all the Excel workbooks on the same directory:

Dim fso, f, fs, f1
Set fso = CreateObject("Scripting.FileSystemObject")
'This can be changed to reflect a different path
Set f = fso.getfolder(ActiveWorkbook.Path)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = "xls" Then
'Here you do whatever you need with the workbook
ProcessWorkbook f1.Name
End If
Next


--
It is nice to be important, but it is more important to be nice


"Miss Atlanta" wrote:

How do I combine multiple workbooks in Excel using a Macro?

Sorry, I made a mistake on the code, calling the recurrence wrong. This is
the code that works:

OpenFiles "C:\whatever directory"
sub OpenFiles(folderspec)
Dim fso, f, f1, s, sf
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(folderspec)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = "xls" Then
CopyData folderspec & "\" & f1.name
End If
Next
Set sf = f.SubFolders
For Each f1 in sf
OpenFiles folderspec & "\" & f1.name
Next
end Sub
--
It is nice to be important, but it is more important to be nice


"Miguel Zapico" wrote:

It can be done, here is some code that will trigger the data gathering for
all the files found in a directory and its subfolders:

OpenFiles "C:\whatever directory"
sub OpenFiles(folderspec)
Dim fso, f, f1, s, sf
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(folderspec)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = "xls" Then
CopyData folderspec & "\" & f1.name
End If
Next
Set sf = f.SubFolders
For Each f1 in sf
main folderspec & "\" & f1.name
Next
end Sub

You have to create the Sub CopyData with your special data needs, but
usually you have to do four things:
1.- Open the file that comes as a parameter to the subroutine
2.- Copy the information from the file, using whatever method is more
appropiate
3.- Paste the information in your consolidation file
4.- Close the file

This sub is too dependant of the type of data and the organization of the
files that I cannot give any valid example.

I hope this helps.

--
It is nice to be important, but it is more important to be nice


"Miss Atlanta" wrote:

Thanks! Here is my situation:
I need to write a macro that will pull data(excel files) from different
folders in the same directory onto a "master" workbook that results in the
compiling of each file in the folders on to one sheet. Is that possible? My
Manager is needing this like Yesterday! (Please help again if you can)

"Miguel Zapico" wrote:

I don't know what you mean as "combine", but I use this code to cycle through
all the Excel workbooks on the same directory:

Dim fso, f, fs, f1
Set fso = CreateObject("Scripting.FileSystemObject")
'This can be changed to reflect a different path
Set f = fso.getfolder(ActiveWorkbook.Path)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = "xls" Then
'Here you do whatever you need with the workbook
ProcessWorkbook f1.Name
End If
Next


--
It is nice to be important, but it is more important to be nice


"Miss Atlanta" wrote:

How do I combine multiple workbooks in Excel using a Macro?