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?
;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(14 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 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=..."
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.
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 )
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!
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
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)
;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(14 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:
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 ;-)
;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(08);;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
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