CODEWITHSUNDEEP
pythonexcelpandas
mk8efz
mk8efz

Reputation: 1424

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:

Refresh External Data

I'm using Python 2.7 and am relying on Pandas for most of the Excel data parsing.

Upvotes: 23

Views: 89384

Answers (7)

Kane Chew
Kane Chew

Reputation: 4123

I am reposting a more complete answer based on the various errors I encountered when I tried existing solutions:

# Open an Instance of Application
xlapp = win32com.client.DispatchEx("Excel.Application") 

# Open File
Workbook = xlapp.Workbooks.Open(<path+file name>) 

# Refresh all  
Workbook.RefreshAll()

# Wait until Refresh is complete
xlapp.CalculateUntilAsyncQueriesDone()

# Save File  
Workbook.Save()

# Quit Instance of Application
xlapp.Quit() 

#Delete Instance of Application
del xlapp

Upvotes: 1

henry434
henry434

Reputation: 107

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

Upvotes: 1

Andrei Harin
Andrei Harin

Reputation: 51

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

Upvotes: 4

Xaviour
Xaviour

Reputation: 31

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

Upvotes: 3

Chris J
Chris J

Reputation: 461

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

Upvotes: 27

ratherstrange
ratherstrange

Reputation: 117

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

Upvotes: 6

Julien Marrec
Julien Marrec

Reputation: 11905

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

Upvotes: 18

Related Questions