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

[Miss Atlanta]

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

[Miguel Zapico]

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?

[Tom Ogilvy]

See sample code at Ron de Bruin's site:

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

--
Regards,
Tom Ogilvy


"Miss Atlanta" <Miss Atlanta@discussions.microsoft.com> wrote in message
news:037E1A55-7EB6-4D9D-BCD3-F71157C14770@microsoft.com...
> How do I combine multiple workbooks in Excel using a Macro?

[Miss Atlanta]

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?

[Miguel Zapico]

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?

[Miguel Zapico]

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?