Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Combine Excel files.
#1
4 files in a folder with sheets:

filename1="C:\01JAN10.xls"
sheet1="INCHES"
sheet2="PREPRNT"
sheet3="ADV"
sheet4="PGS"

filename2
="C:\ILINCH11.xls"
sheet5="SUMMARY"
sheet6="COMP"

filename3
="C:\ININCH11.xls"
sheet7="SUMMARY"
sheet8="COMP"

filename4
="C:\TMC11.xls"
sheet9="SUMMARY"

I want to create a master file that has all of the sheets in one workbook and maintain the formatting in the sheets.
Is this possible? I know the sheets will need a unique name in the end...so could prefix filename to beginning of sheet name.
So the end result will be one file with all the sheets:
filename="c:/MasterFile.xls"
sheet1="01JAN10 INCHES"
sheet2="01JAN10 PREPRNT"
sheet3="01JAN10 ADV"
sheet4="01JAN10 PGS"
sheet5="ILINCH11 SUMMARY"
sheet6="ILINCH11 COMP"
sheet7="ININCH11 SUMMARY"
sheet8="ININCH11 COMP"
sheet9="TMC11 SUMMARY"

Really it's the part of maintaining the format that I don't understand....getting data from a specific sheet in a workbook has been covered several times. But how to copy data with formatting?

Thanks,
jimmy Vig
#2
hi,

Im interested in this solution too!

TheVig,
Did you got the solution?

BR,
#3
I never got a solution to this.
#4
humm Sad

Gintaras,
can you give us a hand here?
#5
Macro Excel combine workbooks
Code:
Copy      Help
;This macro copies all sheets from multiple Excel workbook files to the current workbook. From all xls files in a folder.

;HOW TO USE
;1. Change folder path in this macro. It is where are your Excel workbook files (xls).
;2. In Excel create new empty workbook. Save somewhere in other folder. You must save it before running this macro.
;3. Run this macro and wait.
;4. Delete the first 3 empty sheets, because this macro does not delete existing sheets.

;NOTES
;This macro temporarily opens each file in the same Excel instance.

out
str folder="$Documents$\Excel" ;;change this

;---------------------------

ExcelSheet es esm.Init
Excel.Workbook wb=esm.ws.Parent

Dir d
foreach(d F"{folder}\*.xls" FE_Dir) ;;for each file
,str sPath=d.FileName(1)
,out sPath
,str filename=d.FileName; filename.fix(filename.len-4)
,es.Init(1 4 sPath)
,Excel.Workbook wb2=es.ws.Parent
,rep ;;for each sheet
,,str name.from(filename " " es.ws.Name)
,,out name
,,es.ws.Copy(@ wb.Sheets.Item(wb.Sheets.Count))
,,wb.ActiveSheet.Name=name
,,es.ws=es.ws.Next; if(!es.ws) break
,wb2.Close
#6
Hi Gintaras,

I tested your code with one example file. It copy one sheet but fails in the next ones! The sheet that is selected when you save the original file is copy well but the remain return a RT error:
Error (RT) in ExcelCopySheets: 0x800A03EC, . ?
in the line:
Excel.Worksheet wsNew=wb.Sheets.Item(wb.Sheets.Count); wsNew.Name=name
right before the "...wsNew.Name=..."
#7
Maybe a sheet with that name already exists in the master workbook?
#8
It seams that it's not the problem!

The source file has the following sheets:
xxx, AAA, BBB, CCC

the Master file has:
Folha1, Folha2, Folha3
#9
Is this true?

In Excel is workbook with 3 worksheets: Folha1, Folha2, Folha3.
You run the macro. It copies sheet xxx, and fails to copy sheet AAA. Finally you have 4 sheets in Excel: Folha1, Folha2, Folha3, xxx.
#10
Yes, its true.

And if i open the source file and save the file with the sheet 'BBB' active, the macro copy that sheet to the master file and fails the other ones!
#11
Try the updated version.
#12
Got it working! Smile

i commented the following code:
Excel.Worksheet wsNew=wb.Sheets.Item(wb.Sheets.Count);; wsNew.Name=name
......................................................................^

Try to understand what this peace of code does and it seams the rest of the code dont need it!

Many thanks for the excelent support! (as always Tongue)

best regards,
#13
This line renames the new sheet.
#14
Humm! but with that peace of code commented, the name on the master file is changed anyway!

If im not wrong, the "es.ws.Copy(..." automatically changes the name on the target file! Im not sure, cuz im learning all this Excel vs QM process!
#15
If the new name is correct, then don't need that line.

If need to rename, but that line gives error, try to find other way to rename. I don't know why it renames only 1 sheet, and then error.
#16
Maybe it will never renames the sheet!
If what i wrote is correct with the post above(the rename is automatically with Copy method) , the first time it tries to rename, fails!
#17
Yes Smile

Maybe Excel does not like some characters in new name.

Try this macro. It renames the last sheet.
Macro Macro1526
Code:
Copy      Help
ExcelSheet esm.Init
Excel.Workbook wb=esm.ws.Parent
Excel.Worksheet wsLast=wb.Sheets.Item(wb.Sheets.Count) ;;get last sheet object
out wsLast.Name ;;show old name
wsLast.Name="test" ;;rename
#18
Try to replace that line to

wb.ActiveSheet.Name=name
#19
Gintaras Wrote:Yes Smile

Maybe Excel does not like some characters in new name.

Try this macro. It renames the last sheet.
Macro Macro1526
Code:
Copy      Help
ExcelSheet esm.Init
Excel.Workbook wb=esm.ws.Parent
Excel.Worksheet wsLast=wb.Sheets.Item(wb.Sheets.Count) ;;get last sheet object
out wsLast.Name ;;show old name
wsLast.Name="test" ;;rename

This peace of code works like a charm!

Quote:Try to replace that line to

wb.ActiveSheet.Name=name

this change returns an RT error saying that "the name is invalid...". (In Pt language, so this message is get direct from excel)
#20
Gintaras Wrote:Macro Excel combine workbooks
Code:
Copy      Help
;This macro copies all sheets from multiple Excel workbook files to the current workbook. From all xls files in a folder.

;HOW TO USE
;1. Change folder path in this macro. It is where are your Excel workbook files (xls).
;2. In Excel create new empty workbook. Save somewhere in other folder. You must save it before running this macro.
;3. Run this macro and wait.
;4. Delete the first 3 empty sheets, because this macro does not delete existing sheets.

;NOTES
;This macro temporarily opens each file in the same Excel instance.

out
str folder="$Documents$\Excel" ;;change this

;---------------------------

ExcelSheet es esm.Init
Excel.Workbook wb=esm.ws.Parent

Dir d
foreach(d F"{folder}\*.xls" FE_Dir) ;;for each file
,str sPath=d.FileName(1)
,out sPath
,str filename=d.FileName; filename.fix(filename.len-4)
,es.Init(1 4 sPath)
,Excel.Workbook wb2=es.ws.Parent
,rep ;;for each sheet
,,str name.from(filename " " es.ws.Name) <-----------------------------------------
,,out name
,,es.ws.Copy(@ wb.Sheets.Item(wb.Sheets.Count))
,,wb.ActiveSheet.Name=name
,,es.ws=es.ws.Next; if(!es.ws) break
,wb2.Close

I got the problem. On the above code, on the line marked, you are concantenate the sheetName with the filename!
The file name i have has 36 charaters in his name! More than the limit posible to rename a sheet! (31) :wink:

As we say here: The material is always right Big Grin

Many Thanks

Regards,
#21
I messed around with this a bit. I figured out how to create a new excel file. For some reason it has to run in a separate thread to create the file. Probably comes from not really understanding the code I borrowed it from.

Aslo I had it insert the sheets at the beginning. I wonder if there is a way to delete Sheet1, Sheet2, and Sheet3?

Perhaps Gintaras could shed some light on making this run smoother ;-)

Function MergeExcel
Code:
Copy      Help
str mFile

mFile.timeformat("$desktop$\{yyyy}-{MM}-{ss}.xls")
mFile.expandpath

mac "CreateNewExcel" "" mFile
1

Excel.Application app._create
Excel.Workbook book=app.Workbooks.Open(_s.expandpath(mFile))
app.Visible=-1

str folder="$Documents$\Excel" ;;change this

;---------------------------
ExcelSheet esm.Init
ExcelSheet es
Excel.Workbook wb=esm.ws.Parent
int i


Dir d
foreach(d F"{folder}\*.xls" FE_Dir) ;;for each file
,str sPath=d.FileName(1)
,out sPath
,str filename=d.FileName; filename.fix(filename.len-4)
,es.Init(1 4 sPath)
,Excel.Workbook wb2=es.ws.Parent
,rep ;;for each sheet
,,str name.from(filename " " es.ws.Name)
,,out name
,,es.ws.Copy(wb.Sheets.Item(wb.Sheets.Count-2) @)
,,wb.ActiveSheet.Name=name
,,es.ws=es.ws.Next; if(!es.ws) break
,wb2.Close
book.Save

Function CreateNewExcel
Code:
Copy      Help
function ~mFile

mFile.expandpath

ExcelSheet New.Init("" 8)
New.ws.Application.ActiveWorkbook.SaveAs(mFile Excel.xlNormal @ @ @ @ 1)
New.ws.Application.Quit
#22
Macro Excel combine workbooks2
Code:
Copy      Help
;This macro creates new Excel workbook on desktop, and copies all sheets from multiple Excel workbook files to it. From all xls files in a folder.

;HOW TO USE
;1. Change folder path in this macro. It is where are your Excel workbook files (xls).
;2. Change new file path/name in this macro.
;3. Run this macro and wait. At the end it will open the new file.

out
str folder="$Documents$\Excel" ;;change this
str newFileName.timeformat("$desktop$\{yyyy}-{MM}-{dd} {HH}-{mm}-{ss}.xls") ;;change this

;---------------------------

;create and save master workbook
ExcelSheet esm.Init(0 8) ;;I used ExcelSheet because its dtor calls Application.Quit
Excel.Application xlApp=esm.ws.Application
Excel.Workbook wbm=xlApp.ActiveWorkbook
wbm.SaveAs(_s.expandpath(newFileName) Excel.xlNormal @ @ @ @ 1) ;;if fails, possibly filename contains illegal characters, or the file exists

;copy sheets from other workbooks
Dir d
foreach(d F"{folder.expandpath}\*.xls" FE_Dir) ;;for each file
,str sPath=d.FileName(1)
,out sPath
,str filename=d.FileName; filename.fix(filename.len-4)
,Excel.Workbook wb=xlApp.Workbooks.Open(sPath)
,Excel.Worksheet ws
,foreach ws wb.Sheets ;;for each sheet
,,ws.Copy(@ wbm.Sheets.Item(wbm.Sheets.Count))
,,str name.from(filename " " ws.Name)
,,out name
,,wbm.ActiveSheet.Name=name ;;if fails, probaby name is too long. Somehow make shorter. Also fails if a sheet with this name already exists.
,wb.Close

;delete first 3 empty sheets, select first sheet, save
rep(3) wbm.Sheets.Item(1).Delete
wbm.Sheets.Item(1).Activate
wbm.Save
wbm.Close ;;thank you TheVig

;open in Excel to see
run newFileName
#23
Brilliant!

Only change I had to make was added wbm.Close after the wbm.Save. Function was keeping an instance of Excel open that locked the file.
-jim
#24
So I was able to piece together a dialog using all of this that allow you to be able to choose sheets to merge from a directory.

I used an HTML form in the dialog because it was the easiest to format the way I wanted it to work.

The end file gets exported to the desktop with the date as the file name.

I didn't play much around with the dialog layout. I'd like to have a browse button/edit box that would build the form after the dialog was initialized.
It would probably be a good idea to have a place to specify the output file too...Perhaps the file should just save in $temp$. That way the user can save the output file however they want when Excel opens it at the end of the merge.

Overall, a lot of leg work is finished on this project. Thank you Gintaras for all of your hard work! I'm glad I can finally have this function working for future projects!
-Jim


Attached Files
.qml   MergeSheets.qml (Size: 3.64 KB / Downloads: 363)


Forum Jump:


Users browsing this thread: 1 Guest(s)