Python Script to Automate Refreshing an Excel Spreadsheet

Question

I would like to automatically refresh my excel file. Currently the code I have is not completely satisfactory because Excel asks me each time to click Cancel or OK. I have two sheets in the Excel file.

Here is the python code:

import win32com.client as win32
Xlsx = win32.DispatchEx('Excel.Application')
Xlsx.DisplayAlerts = True
Xlsx.Visible = True
book = Xlsx.Workbooks.Open('C:/Test_Excel/Essai_1.xlsx')
# Refresh my two sheets
book.RefreshAll()
book.Save()
book.Close()
Xlsx.Quit()
del book
del Xlsx

How to automatically refresh the Excel file without Excel asking me any questions.

Thank you

Answer 1

Try setting Xlsx.DisplayAlerts = True to Xlsx.DisplayAlerts = False, that should do the trick.

Answer 2

refreshall() is an async function so you need to wait for it to finish before you do anything else. you can use Xlsx.CalculateUntilAsyncQueriesDone() to accomplish this.

import win32com.client as win32
Xlsx = win32.DispatchEx('Excel.Application')
Xlsx.DisplayAlerts = True
Xlsx.Visible = True
book = Xlsx.Workbooks.Open('C:/Test_Excel/Essai_1.xlsx')
# Refresh my two sheets
book.RefreshAll()
Xlsx.CalculateUntilAsyncQueriesDone()# this will actually wait for the excel workbook to finish updating
book.Save()
book.Close()
Xlsx.Quit()
del book
del Xlsx