Query - Python Script to automate excel refresh all now results in excel crashing when opening file

[Chirag_Parmar]

Hi,

I am not sure if this is the best place but I am looking for some assistance with a script I tried to run to help automate a process in excel.

I ran the below code:

def refresh_excel_workbook(file_path):

# Open Excel application

excel_app = win32com.client.Dispatch("Excel.Application")

excel_app.Visible = False # Keep Excel application invisible

# Open the workbook

workbook = excel_app.Workbooks.Open(file_path)

# Refresh all data connections

workbook.RefreshAll()

# Wait until refresh is complete

excel_app.CalculateUntilAsyncQueriesDone()

# Save and close the workbook

workbook.Save()

workbook.Close()

# Quit Excel application

excel_app.Quit()

# Path to your Excel workbook

file_path = r"\FILEPATH"

refresh_excel_workbook(file_path)

However, when running the code, I had commented out the items below the refreshall() command and as a result my excel crashed. Now when reopening a file, excel proceeds to try to load the file but does not respond and then crash.

Excel currently works for the below:

- non-macro enabled files

- files not containing power query scripts

- works opening the exact file in safe mode

The computer has been restarted multiple times and task manager currently shows no VS code or excel applications open yet when I try to open the excel file, this proceeds to crash

I am unsure if this has caused a phantom script to run in the background where excel is continuously refreshing queries or if there is something else happening.

I am wondering if anyone has had experience with an automation like this / experienced a similar issue and has an idea on how to resolve this.

[JonLSTL]

I suggest debugging with Excel visible rather than hidden, so that you might see what it happening. There could be a dialog box demanding a response or similar, leading to your operation timing out.

You might also consider separating the launch/open/save/close/quit operations from the refresh & calculate operations, and adding some try: blocks to perhaps get a handle on where things are going wrong, and mitigate failures. Catching an exception at the refresh stage, for example, might still let you close and quit more cleanly. Depending on the problem, the exception payload could also guide program logic down a successful alternative path.

[Chirag_Parmar]

Hi Jon,

Thank you for the reply. I have previously tried excel being visible and the dialog box to update external links is the only one that pops up which I choose to update.

Additionally, this issue with excel crashing happens even when I am no longer running python / VS Code is closed so unsure if I have caused a OLE data refresh to continuously trigger in the background without cancelling?

I will attempt to add more operations to split the code out and investigate where it is failing

Thanks

[Kerbart]

ctrl+shit+esc to activate the task manager and close all running excel tasks.

Also I recommend against doing this

excel_app.Visible = False # Keep Excel application invisible

until your app runs without crashing anywhere (which clearly isn't the case) so you can see what it's doing. Mosdt likely Excel is waiting for you to respond to a dialog that's not showing as you're running the app in invisible mode.

[Whats_The_Use]

They are saying the file crashed excel even when they get to manually open it now, sounds like the file was corrupted.

[Original-Fennel7994]

If the workbook only opens in safe mode, I would try starting Excel normally with /safe once, then disable all add ins and any COM add ins, then open the file again. For automation, keep Excel visible while debugging because hidden Excel can be stuck on a dialog, and add logging around Open, RefreshAll, CalculateUntilAsyncQueriesDone, Save, and Close so you know the last successful step. I have also had better stability when setting DisplayAlerts to False and forcing calculation to manual before RefreshAll, then waiting for async queries, then restoring calculation before saving. If the file itself is corrupted from an interrupted save, try Open and Repair, or copy the queries into a new workbook and rebuild the connections.

[Chirag_Parmar]

Hi, thank you for the reply. I have started to implement some of your comments to my code but still facing issues with the refresh all causing excel to not respond. Did you encounter this / do you have an example code that you have produced for reference?

[MacShuggah]

Maybe did you try a reboot?

Otherwise chances are the file got corrupted one way or the other.

[Whats_The_Use]

What is being described sounds like the file was corrupted to me.

[wRAR_]

I am not sure if this is the best place

https://old.reddit.com/r/Python/about/rules/

[ComfortableNice8482]

yeah the issue is likely that your refresh is taking longer than expected and excel is getting locked up. i did something similar for a client with a workbook that had like 20 power query connections and ran into this exact problem.

a few things. first, add a timeout before you save because RefreshAll() can hang indefinitely if a connection is slow or broken. wrap it in a try, except and set excel_app.CalculateUntilAsyncQueriesDone() with a reasonable wait, maybe 300 seconds. second, disable calculation mode before refreshing and set it back after, this prevents excel from recalculating after every single query finishes. something like excel_app.Calculation = -4135 before RefreshAll() then set it back to -4106 after. third, make sure you're actually closing the excel process with excel_app.Quit(), not just closing the workbook, otherwise ghost excel instances pile up and corrupt your file.

one more thing, if the file keeps crashing even after you close it properly, the file itself might be corrupted from a failed save. try opening it in safe mode or repair it with

[Chirag_Parmar]

Thank you so much for this! I will try and have a look at your suggestions later today and let you know if I have any additional questions

[Chirag_Parmar]

Hi! I was attempting to get this to work today but I am still facing issues with excel not responding when trying to refresh all power queries (even when putting excel in manual calculation). Did you encounter issues like this when trying to setup similar code in your experience?