SLOW Excel Performance

[taburete68]

I've got a script that essentially does a mail merge with Excel, Copying the cells from one sheet to another, saving the second sheet and going on to the next row in the first sheet. The first sheet has about 3000 rows and goes extremely slow. Perhaps this is just a caveat of an interpreted language. Here's the code. Is there something I'm doing to slow it up? Right now it takes about 45 minutes to complete. Making the docs visible slows it down a bit. I also am doing the savecopyas, that way I don't have to reopen blank.xls. That helped a little bit as well. It seems that it's the file save stuff that is the slowest. I'm only using about 10% of my cpu for this task.

path := "C:\test\"
opath := "C:\test\output"

iExcel := ComObjCreate("Excel.Application")
iExcel.Workbooks.Open(path . "Input Data.xlsx")
;iExcel.Visible := True

oExcel := ComObjCreate("Excel.Application") ; Excel to Loop
oExcel.Workbooks.Open(path . "blank.xlsx") ;
;oExcel.Visible := True

Row := 4 ;First Row of Data in iExcel
while (iExcel.Range("A" . Row).Value != "")
{

oExcel.Range("C3").Value := iExcel.Range("A" . Row).Value ;
oExcel.Range("E3").Value := iExcel.Range("B" . Row).Value ;
oExcel.Range("G3").Value := "0001" ;
;move a bunch more text around.
oExcel.ActiveSheet.Shapes.AddPicture("C:\test\Sample.png", True, True, 100, 550, -1, -1) ;

oExcel.ActiveWorkbook.SaveCopyAs(opath . iExcel.Range("F" . Row).Value . "\" . iExcel.Range("C" . Row).value . " " . iExcel.Range("I" . Row).value . ".xlsx")
oExcel.ActiveWorkbook.ExportAsFixedFormat( 0, opath . iExcel.Range("F" . Row).Value . "\PDF\" . iExcel.Range("C" . Row).value . " " . iExcel.Range("I" . Row).value)
Row := Row + 1
}

iExcel.ActiveWorkbook.Close
iExcel.Quit
oExcel.Quit

ExitApp

[RandomAHKUser]

Have you tried setting Excel.Visible := False and then only setting it to True after the calculations? They should be much quicker when it doesn't have to display the data when processing.

Edit: looks like you've tried this already. The performance should not be slow based on the coding, as you are passing information directly to Excel via it's COM OBJ. All I can guess is that it is an intensive process for excel and to try to find a smarter way to work with the information?

[FanaticGuru]

I've got a script that essentially does a mail merge with Excel, Copying the cells from one sheet to another, saving the second sheet and going on to the next row in the first sheet. The first sheet has about 3000 rows and goes extremely slow. Perhaps this is just a caveat of an interpreted language. Here's the code. Is there something I'm doing to slow it up? Right now it takes about 45 minutes to complete. Making the docs visible slows it down a bit. I also am doing the savecopyas, that way I don't have to reopen blank.xls. That helped a little bit as well. It seems that it's the file save stuff that is the slowest. I'm only using about 10% of my cpu for this task.

I don't think the slow performance has anything to do with AHK. The bottleneck is the SaveAs and Export as a PDF. Saving a file and also creating a PDF and saving that is literally taking your hard drive a second (or close to it). Doing that 3000 times creating and saving 6000 files is taking 45 minutes.

Reading in a png from the hard drive 3000 times also is not helping speed. It is possible to read that file in once and then reuse it but I imagine the real time consumer is the creation and saving of the PDF.

I don't know if there are any options in Excel that can speedup its ability to create and save PDFs but that is where you need to look for optimizations. AHK is telling Excel what to do way, way faster than Excel can preform those actions.

FG

[Guest]

I tried running the script without the save as xls and save as PDF. It runs faster, but it's still not as speedy as I'd thought it would be. I'm not sure of the Excel overhead to do these things, but it seems pretty high. The long time isn't really a problem, it's just a pain to wait 45 minutes for it to complete. Thanks for your help.

[sinkfaze]

You still have the problem that you're loading some lines over and over and over that appear to only need to be loaded once:

oExcel.Range("G3").Value := "0001"

oExcel.ActiveSheet.Shapes.AddPicture("C:\test\Sample.png", True, True, 100, 550, -1, -1)

Move those two lines so that they execute just before starting the loop, since there's no apparent need for them to repeat.

[Blackholyman]

Try this out

Code: Select all

SetBatchLines -1
path := "C:\test\"
opath := "C:\test\output"

iExcel := ComObjCreate("Excel.Application")
iExcel.Workbooks.Open(path . "Input Data.xlsx")
;iExcel.Visible := True

oExcel := ComObjCreate("Excel.Application") ; Excel to Loop
oExcel.Workbooks.Open(path . "blank.xlsx") ; 
;oExcel.Visible := True

SArr := iExcel.Range("A" . Start_row := 4 . ":" RegExReplace(iExcel.ActiveSheet.Cells(iExcel.ActiveSheet.Cells(iExcel.Rows.Count, "A").End(xlUp := -4162).row, iExcel.ActiveSheet.Cells(1, iExcel.Columns.Count).End(xlToLeft := -4159).Column).Address, "\$")).value

oExcel.Range("G3").Value := "0001" ;
oExcel.ActiveSheet.Shapes.AddPicture("C:\test\Sample.png", True, True, 100, 550, -1, -1) ; 

Loop % SArr.MaxIndex(1)
{
oExcel.Range("C3").Value := SArr[A_Index, 1] ;
oExcel.Range("E3").Value := SArr[A_Index, 2] ; 

;move a bunch more text around.

oExcel.ActiveWorkbook.SaveCopyAs(opath . SArr[A_Index, 6] . "\" . SArr[A_Index, 3] . " " . SArr[A_Index, 9] . ".xlsx")
oExcel.ActiveWorkbook.ExportAsFixedFormat( 0, opath . SArr[A_Index, 6] . "\PDF\" . SArr[A_Index, 3] . " " . SArr[A_Index, 9])
}

iExcel.ActiveWorkbook.Close
iExcel.Quit
oExcel.Quit

ExitApp

[taburete68]

Try this out

Code: Select all

SetBatchLines -1
path := "C:\test\"
opath := "C:\test\output"

iExcel := ComObjCreate("Excel.Application")
iExcel.Workbooks.Open(path . "Input Data.xlsx")
;iExcel.Visible := True

oExcel := ComObjCreate("Excel.Application") ; Excel to Loop
oExcel.Workbooks.Open(path . "blank.xlsx") ; 
;oExcel.Visible := True

SArr := iExcel.Range("A" . Start_row := 4 . ":" RegExReplace(iExcel.ActiveSheet.Cells(iExcel.ActiveSheet.Cells(iExcel.Rows.Count, "A").End(xlUp := -4162).row, iExcel.ActiveSheet.Cells(1, iExcel.Columns.Count).End(xlToLeft := -4159).Column).Address, "\$")).value

oExcel.Range("G3").Value := "0001" ;
oExcel.ActiveSheet.Shapes.AddPicture("C:\test\Sample.png", True, True, 100, 550, -1, -1) ; 

Loop % SArr.MaxIndex(1)
{
oExcel.Range("C3").Value := SArr[A_Index, 1] ;
oExcel.Range("E3").Value := SArr[A_Index, 2] ; 

;move a bunch more text around.

oExcel.ActiveWorkbook.SaveCopyAs(opath . SArr[A_Index, 6] . "\" . SArr[A_Index, 3] . " " . SArr[A_Index, 9] . ".xlsx")
oExcel.ActiveWorkbook.ExportAsFixedFormat( 0, opath . SArr[A_Index, 6] . "\PDF\" . SArr[A_Index, 3] . " " . SArr[A_Index, 9])
}

iExcel.ActiveWorkbook.Close
iExcel.Quit
oExcel.Quit

ExitApp

I'm not sure if this accomplishes what I need. I'm looping through each row of the iExcel sheet coping the values from those rows into the oExcel sheet. I don't follow what you're doing with creating the SArr variable, but it looks like you're creating a matrix of the data and then looping on the columns? Again I'm puzzled. Please explain.

Thanks,

[Blackholyman]

did you try the code?

The code makes a SafeArray from the excel data and then loops over the rows so that the code only needs to read the data from iExcel once aka greatly reducing the number of read actions needed...