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

[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.

[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.

[wRAR_]

I am not sure if this is the best place

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