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
SLOW Excel Performance
-
- Posts: 2
- Joined: 06 Jun 2016, 17:07
SLOW Excel Performance
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.
-
- Posts: 45
- Joined: 08 Dec 2015, 22:13
Re: SLOW Excel Performance
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?
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
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: SLOW Excel Performance
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.taburete68 wrote: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.
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
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: SLOW Excel Performance
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.
Re: SLOW Excel Performance
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.
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
- Posts: 1293
- Joined: 29 Sep 2013, 22:57
- Location: Denmark
- Contact:
Re: SLOW Excel Performance
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
Courses on AutoHotkey
My Autohotkey Blog
-
- Posts: 2
- Joined: 06 Jun 2016, 17:07
Re: SLOW Excel Performance
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.Blackholyman wrote:Try this outCode: 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
Thanks,
- Blackholyman
- Posts: 1293
- Joined: 29 Sep 2013, 22:57
- Location: Denmark
- Contact:
Re: SLOW Excel Performance
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...
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...
Courses on AutoHotkey
My Autohotkey Blog
Who is online
Users browsing this forum: No registered users and 345 guests