Excel ComObject leaves excel.exe open after closing?

[JackPJ]

Hi all,

I'm trying to work out how to close an excel comobject correctly without it leaving open a process in the background.

I cant use the get around of ending any process named excel.exe because I may have other copies of excel open at the time.

Could anyone take a look at the below and let me know if I'm closing the comobject incorrectly or something?



Many thanks!

Code: Select all

LRLocation := A_ScriptDir . "\" . "test" . ".xlsm"
xl := ComObjCreate("Excel.Application")
xl.Visible := true
path = %LRLocation%
xl.WorkBooks.Open(Path)

SaveLoc := A_MyDocuments . "\" . StockAccountCode . "- Price List.xlsx"	
Xl.ActiveWorkBook.SaveAs(SaveLoc,51)

xl.quit
xl := ""

[Capn Odin]

See Application.Quit Method, try setting the property DisplayAlerts to false.

[JackPJ]

Thanks for the reply, but I think that's what the 'xl.quit' was doing in my code as xl := excel.application?

I've tried the display alerts already but no luck

I thought I had narrowed it down to a certain part , but it turns out, it's just sporadic in deciding when to leave the process open or not . I just re-ran the script 20+ times and it was a 50:50 split between properly closing the process by itself or leaving it open

[MaxAstro]

In my experience, usually when the same block of script has unpredictable results, it's a timing issue - sometimes the computer moves fast enough to keep up with the script, and sometimes it doesn't.

I would try putting second-long sleeps in between each step of the closing script and see if running it that way causes it to always behave the same way. If it does, remove the sleeps bit by bit and adjust their length until you figure out how much sleep it needs to run reliably.

[kon]

I've tried the display alerts already but no luck

Really? Like this?

Code: Select all

Xl.DisplayAlerts := false  ; Ignore the warning that a macro enabled workbook (.xlsm) is being saved as .xlsx
Xl.ActiveWorkBook.SaveAs(SaveLoc, 51)  ; xlOpenXMLWorkbook = 51
Xl.DisplayAlerts := true

[CesarF]

I ran in the same situation and find some help regarding not only script speed but orphan references when closing... google this just to be sure your code is not going nuts in automation mode, because A LOT of things runs in a very bizarre way with different win & xlsm versions, cpu/disk speed, etc...

[kon]

I ran in the same situation and find some help regarding not only script speed but orphan references when closing... google this just to be sure your code is not going nuts in automation mode, because A LOT of things runs in a very bizarre way with different win & xlsm versions, cpu/disk speed, etc...

Do you have an example to back up that statement?
In my experience, COM automation is very reliable across Windows versions.
On a rare occasion you will run into a feature that is only available in later versions of Excel.
I've never even heard of cpu or disk speed causing any issues.
xlsm is a workbook. What does version of a workbook mean? Do you mean version of Excel?

[JackPJ]

I've tried the display alerts already but no luck

Really? Like this?

Code: Select all

Xl.DisplayAlerts := false  ; Ignore the warning that a macro enabled workbook (.xlsm) is being saved as .xlsx
Xl.ActiveWorkBook.SaveAs(SaveLoc, 51)  ; xlOpenXMLWorkbook = 51
Xl.DisplayAlerts := true

Yes exactly like that. I often use that for deleting worksheets to avoid the "Just so you know, you cant undo this" msg that pops up, so I thought why not try it for the save-as (although I can't think what alert would pop up other than "file already exists" which wouldn't apply here.)

As for CesarF's statement on it being some random factor not related to excel. I've come to the conclusion that it must be the case. I took the script back to its basics of just "open excel comobject -> open workbook -> close excel". And re-ran it over 50 times.
I found that around 1 in 10 times, it still caused the same issue with the process hanging (with workbook visibility set to false). Yet when I set the visibility to true, it went up to about 7 in 10 times. Nothing changed other than that one line... I am at a loss.

You can literally tell immediately when watching task manager when it will or wont happen upon the script running xl.quit .
- If excel is going to close ok, the process hangs for a few seconds with cpu usage maxed out for that core (25% overall for my system), guessing a shutdown procedure in the background?) At which point the cpu drops to 0 usage and the process disappears a moment after.

- If excel is going to hang forever, when xl.quit is run, the cpu never has that 100% spike and just sits at 0%. The ram usage slowly drops but never goes below 60-70mb.

I'm lost. I may even record a video of it later if I get time so you can see that I am literally running the same script repeatedly with different outcomes

[Tre4shunter]

I think this is what you need:

Code: Select all

LRLocation := A_ScriptDir . "\" . "test" . ".xlsm"
xl := ComObjCreate("Excel.Application")
xl.application.displayalerts := 0
xl.Visible := true
path = C:\Users\md.GESINC1.000\Desktop\contacts.xlsx
xla := xl.WorkBooks.Open(Path)

SaveLoc := A_MyDocuments . "\test - Price List.xlsx"	
Xla.SaveAs(SaveLoc,51)

xla.close
xl.quit
xl := ""

obviously change to your correct paths.

Thanks!

[kon]

JackPJ,

Did you see my comment that the warning is because you are saving an xlsm as an xlsx? It works 20/20 times for me. As I said, compatibility issues are rare. COM was designed specifically for that.

Do your xlsm workbooks have any VBA code that is run automatically? (or complex formula calculations) This could be misinterpreted as "hangs" when it does automatic calculations in the background.

[Xeo786]

you can use "comobjget(filepath)" this would do the job while file being invisible .... and would cost no extra process of excel.exe in task manager.