Refresh Excel External Data with Python
I have an Excel file that I run a Python script on. The Excel file has external data connections that need to be refreshed before the Python script is run. The functionality I'm referring to is here:
I'm using Python 2.7 and am relying on Pandas for most of the Excel data parsing.
CalculateUntilAsyncQueriesDone() will hold the program and wait until the refresh has completed.
xlapp = win32com.client.DispatchEx("Excel.Application")
wb = xlapp.Workbooks.Open(<path_to_excel_workbook>)
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
wb.Save()
xlapp.Quit()
If you're on windows, and I believe you are given the screenshot, you can use the win32com
module. It will allow you - from python - to open up Excel, load a workbook, refresh all data connections and then quit. The syntax ends up being pretty close to VBA.
I suggest you install pypiwin32
via pip (pip install pypiwin32
).
import win32com.client
# Start an instance of Excel
xlapp = win32com.client.DispatchEx("Excel.Application")
# Open the workbook in said instance of Excel
wb = xlapp.workbooks.open(<path_to_excel_workbook>)
# Optional, e.g. if you want to debug
# xlapp.Visible = True
# Refresh all data connections.
wb.RefreshAll()
wb.Save()
# Quit
xlapp.Quit()
Adding this as an answer since this is the first Google link - the code in the first answer worked but has incorrect capitalization, it should be:
import win32com.client
import time
xlapp = win32com.client.DispatchEx("Excel.Application")
wb = xlapp.Workbooks.Open(<path_to_excel_workbook>)
wb.RefreshAll()
time.sleep(5)
wb.Save()
xlapp.Quit()