Combining several workbooks into one file

[PJ]

I have 4 individual workbooks that are generated as part of our
monthly/quarterly reporting and want to automate the process of combining
each workbook into one file with 4 worksheets. Each workbook has the same
data/columns and similar naming convention. The name of the worksheet in
each file is the same as the file name (see below).

Sheet Name Workbook Name
FIN_20080630 FIN_20080630.xls
HRS_20080630 HRS_20080630.xls
GEN_20080630 GEN_20080630.xls
ISS_20080630 ISS_20080630.xls

I found the code below in a post by Tom Ogilvy and was able to modify it for
my needs by hard coding the file names. What I would like to do is create a
template with a button to prompt the user for the cycle date and pull the
corresponding files into a new workbook. I would also like to know if it's
possible to run the TRIM function on a specific column in each of the files
as part of the process.

Sub Combinebooks()
Dim sPath as String
Dim bk1 as Workbook
Dim bk2 as Workbook
Dim bk3 as Workbook
Dim bk4 as Workbook

spath = "C:\Documents and Settings\en14259\Desktop\EOM Reports\"

if dir(sPath & "Consolidated20080630.xls") <> "" then
kill sPath & "Consolidated20080630.xls"
End if

set bk1 = workbooks.open(spath & "FIN_20080630.xls")
set bk2 = workbooks.Open(sPath & "HRS_20080630.xls")
set bk3 = workbooks.Open(sPath & "GEN_20080630.xls")
set bk4 = workbooks.Open(sPath & "ISS_20080630.xls")
bk2.worksheets(1).copy After:=bk1.worksheets(1)
bk1.worksheets(2).name = "HRS_20080630"
bk3.worksheets(1).copy After:=bk1.worksheets(2)
bk1.worksheets(3).name = "GEN_20080630"
bk4.worksheets(1).copy After:=bk1.worksheets(3)
bk1.worksheets(4).name = "ISS_20080630"
bk1.worksheets(1).name = "FIN_20080630"
bk1.SaveAs sPath & "Consolidated20080630.xls"
bk1.close Savechanges:=False
bk2.close Savechanges:=False
bk3.close Savechanges:=False
bk4.close Savechanges:=False
End Sub

 

[Ron de Bruin]

Hi PJ

You can try the code in the download of this page
http://www.rondebruin.nl/fso.htm

 

[PJ]

Nice site Ron. I will see if I can get it to work using your example.

Thanks!

 

[PJ]

Hi PJ

You can try the code in the download of this page
http://www.rondebruin.nl/fso.htm

Ron, thank you for your assistance. That worked like a charm. I have two
other "minor" inconveniences as part of this process and was hoping you could
offer a suggestion.

I process each of the original files using a data analytics program and
export my results to Excel. The originals in this case are also Excel files
and each contains a column header with characters that causes problems with
the analytics program so I have to manually rename the column before
importing the files. Second, I combine the data from two columns into one
column before exporting back to Excel and the result needs to be trimmed to
remove the extra spaces.

How can I create a similar macro that will search for the "bad" string and
replace it with a new string before I import my files. And also, is it
possible to modify the existing macro to perform a TRIM on each of the
worksheets, which in each case would be everything in column B.

My hope is to develop a master spreadsheet that can be handed off to a
support person who can click a button to run each macro in the process.
These are the last two issues I need.

Thanks in advance.

 

[JLGWhiz]

This will trim all the cells in the used range. If I understand you
correctly, the source file is also an Excel worksheet. The code would need
to be modified to use the source file destination. The filename must be
compatible with the system either showing the file extension or not.
Otherwise you will probably get a subscript out of range message.

Sub celTrim()
Dim c As Range
SourceFile = Workbooks("?.xls") 'Need to sub actual file name
SourceFile.Sheets(1).UsedRange.Select
For Each c In Selection
c = Trim(c.Value)
Next
End Sub