CODEWITHSUNDEEP
pythonexcelautomationrefresh
Bak
Bak

Reputation: 443

Python Script to Automate Refreshing an Excel Spreadsheet

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

Upvotes: 5

Views: 13565

Answers (2)

Chris J
Chris J

Reputation: 461

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

Upvotes: 3

Nordle
Nordle

Reputation: 2981

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

Upvotes: 2

Related Questions