Refresh Excel External Data with Python

Question

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.

Answer 1

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()

Answer 2

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()