Combine Excel files.

[TheVig]

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