Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining several workbooks into one file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining several workbooks into one file
Hi PJ
You can try the code in the download of this page http://www.rondebruin.nl/fso.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PJ" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining several workbooks into one file
Nice site Ron. I will see if I can get it to work using your example.
Thanks! "Ron de Bruin" wrote: Hi PJ You can try the code in the download of this page http://www.rondebruin.nl/fso.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "PJ" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining several workbooks into one file
"Ron de Bruin" wrote:
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining several workbooks into one file
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 "PJ" wrote: "Ron de Bruin" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COMBINING WORKBOOKS | Excel Programming | |||
Combining 3 workbooks to one | Excel Programming | |||
combining workbooks | Excel Programming | |||
combining 2 workbooks | Excel Discussion (Misc queries) | |||
Combining workbooks | Excel Discussion (Misc queries) |