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

Answer 3

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

Answer 4

Adding on top of what everyone else has said, I kept getting the save dialog again when the code got to the Quit line. I set the DisplayAlerts flag to false and it fixed my issue. I didn't need the sleep timer either. This is what worked for me:

xlapp = win32com.client.DispatchEx("Excel.Application")
wb = xlapp.Workbooks.Open(<path_to_excel_workbook>)
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
xlapp.DisplayAlerts = False
wb.Save()
xlapp.Quit()

Answer 5

A small note, but important one. All the codes above are correct, but it will raise the issue with permission Err 13 because the file is only being saved, not closed as well.

add wb.Close() after save, otherwise the openned Excel will remain in the background app, and if you work with 500 of those, you might get a bit into troubles

Answer 6

Adding another slightly changed answer as I was stumped by this and none of the solutions were working. What worked for me was enabling Xlsx.DisplayAlerts = True and Xlsx.Visible = True, then at end saving the book with book.Save() and also closing with save: book.Close(SaveChanges=True).

It's a bit cumbersome with Excel opening and closing every time (I am iterating through many excel files), but it works so thats good.

import win32com.client as win32
import pythoncom

def open_close_as_excel(file_path):
    try:
        pythoncom.CoInitialize()
        Xlsx = win32.DispatchEx('Excel.Application')
        Xlsx.DisplayAlerts = True
        Xlsx.Visible = True
        book = Xlsx.Workbooks.Open(file_path)
        book.RefreshAll()
        Xlsx.CalculateUntilAsyncQueriesDone()
        book.Save()
        book.Close(SaveChanges=True)
        Xlsx.Quit()
        pythoncom.CoUninitialize()

        book = None
        Xlsx = None
        del book
        del Xlsx
        print("-- Opened/Closed as Excel --")

    except Exception as e:
        print(e)

    finally:
        # RELEASES RESOURCES
        book = None
        Xlsx = None