kon wrote:JackPJ wrote: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