Refresh Excel Data Connections and Recalculate with win32com

The value of driving Excel is often not the data but the calculation: a model whose formulas belong to somebody else, a Power Query connection pointing at a warehouse, a pivot cache that has to be rebuilt before the summary sheet means anything. All three are one method call away — and all three have a timing problem that produces stale output without any error. This guide, part of Automating Excel with COM and pywin32, covers the calls and the waiting.

Prerequisites

pip install pywin32

A workbook with something to refresh — a query, a pivot table, or formulas that depend on data your script writes.

The refresh that is not finished when it returns

RefreshAll triggers every connection and pivot cache in the workbook and returns straight away. If the next line saves the file, the saved workbook contains whatever was there before.

import win32com.client as win32

excel = win32.DispatchEx("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False
try:
    book = excel.Workbooks.Open(r"C:\finance\model.xlsx", UpdateLinks=0)
    try:
        book.RefreshAll()
        excel.CalculateUntilAsyncQueriesDone()      # block until queries finish
        book.Save()
    finally:
        book.Close(SaveChanges=False)
finally:
    excel.Quit()

CalculateUntilAsyncQueriesDone is the simplest fix and covers most workbooks. It waits for outstanding queries and then recalculates, which is exactly the sequence a person performs by pressing Refresh All and waiting for the status bar to settle.

Making refreshes synchronous instead

The more dependable approach removes the asynchrony rather than waiting it out. Every connection has a BackgroundQuery flag, and turning it off makes Refresh block until the data has arrived.

for connection in book.Connections:
    try:
        connection.OLEDBConnection.BackgroundQuery = False
    except Exception:
        connection.ODBCConnection.BackgroundQuery = False
    connection.Refresh()

The try/except is not laziness — a connection exposes either an OLEDBConnection or an ODBCConnection depending on its type, and accessing the wrong one raises. Iterating this way also gives you per-connection control, so a slow warehouse query can be skipped when only a local table needs updating.

Recalculating deliberately

Excel has three levels of recalculation and they are not interchangeable. Calculate recomputes what the dependency tree marks as dirty. CalculateFull recomputes everything. CalculateFullRebuild rebuilds the dependency tree first, which matters after a script has written formulas — Excel's graph can be stale in exactly that case.

XL_MANUAL, XL_AUTOMATIC = -4135, -4105

excel.Calculation = XL_MANUAL          # stop recalculating after every write
sheet = book.Sheets("Data")
sheet.Range("E2:E5001").Formula = "=C2*D2"
excel.CalculateFullRebuild()           # rebuild the graph, then compute
excel.Calculation = XL_AUTOMATIC

Writing five thousand formulas with calculation on automatic makes Excel recompute the workbook five thousand times. Switching to manual first, then rebuilding once, is the difference between seconds and minutes — and it is the same pattern as the file-level advice in Recalculate Excel Formulas Without Excel in Python, approached from the other side.

Pivot tables and their caches

A pivot table reads from a cache, and the cache reads from the source. Refreshing the wrong one produces a table that looks updated and is not.

for sheet in book.Sheets:
    for pivot in sheet.PivotTables():
        pivot.PivotCache().Refresh()      # pull new data into the cache
        pivot.RefreshTable()              # rebuild the table from the cache

RefreshTable alone rebuilds from a cache that may be hours old. PivotCache().Refresh() alone updates the cache but can leave the visible table showing the previous layout. Doing both, in that order, is the reliable sequence. The pure-Python alternatives — where a pivot is generated rather than refreshed — are in Creating Pivot Tables from Excel Data.

Verifying the refresh actually happened

The failure this guide exists to prevent is silent, so the defence is a check rather than a convention. Stamp the refresh time into the workbook and assert on a value you know should change.

from datetime import datetime

before = book.Sheets("Summary").Range("C12").Value
book.RefreshAll()
excel.CalculateUntilAsyncQueriesDone()
after = book.Sheets("Summary").Range("C12").Value

book.Sheets("Summary").Range("A1").Value = f"Refreshed {datetime.now():%Y-%m-%d %H:%M}"
if before == after:
    print("warning: the headline figure did not move — check the connection")
book.Save()

A visible timestamp on the sheet costs one line and answers the question every recipient eventually asks. The comparison catches a connection that failed quietly, which is the more common of the two failure modes.

Polling a refresh that has no synchronous mode

Some connection types — notably certain Power Query and OLAP sources — ignore BackgroundQuery or do not expose it at all. For those, the option left is to poll, and the property to watch is Refreshing on the connection object.

import time

def wait_for_refresh(book, timeout=600, interval=2):
    deadline = time.monotonic() + timeout
    while time.monotonic() < deadline:
        busy = [c.Name for c in book.Connections if _is_refreshing(c)]
        if not busy:
            return
        time.sleep(interval)
    raise TimeoutError(f"connections still refreshing after {timeout}s: {busy}")


def _is_refreshing(connection):
    for attribute in ("OLEDBConnection", "ODBCConnection"):
        try:
            return getattr(connection, attribute).Refreshing
        except Exception:
            continue
    return False

A timeout is not optional here. A query pointed at a warehouse that is down will otherwise leave the script — and the EXCEL.EXE process behind it — waiting indefinitely, which is precisely the scenario that fills a machine with orphans overnight. Raising after ten minutes turns that into a failed run that the scheduler can report, and the cleanup rules in Close Excel Cleanly and Avoid Orphan COM Processes make sure the process still exits.

Credentials, prompts and unattended runs

The other reason a refresh hangs has nothing to do with timing: the connection wants a password. Interactively, Excel asks; unattended, it asks a dialog nobody can see. There are three workable arrangements, and it is worth choosing one deliberately rather than discovering the problem at 06:00.

Store the credential in the connection string and let the workbook keep it — simple, and only acceptable where the file's own access controls are strong enough to hold a password. Use integrated authentication so the connection runs as the Windows account executing the script, which is the cleanest option when the warehouse supports it. Or take the connection out of the workbook entirely and query the database from Python, writing the results into the sheet — which removes the whole category of problem and is usually the right long-term answer, as Export SQL Query Results to Excel with Python describes.

The third option also makes the job testable. A Python query can be run against a development database from a laptop; a refresh embedded in a workbook can only be exercised by opening the workbook on a machine with the right credentials, which means it is never covered by anything resembling a test.

Common pitfalls

Performance and scale

The expensive part of a refresh is nearly always the query rather than Excel. That has a practical consequence: if a workbook has six connections and the report only depends on two, iterating book.Connections and refreshing the two you need can turn a fifteen-minute job into a two-minute one, with no change to the output.

Recalculation cost scales with the dependency graph rather than with the sheet size, so a workbook with a few thousand volatile functions — NOW, RAND, OFFSET, INDIRECT — can be slower than one with a hundred thousand simple formulas. When a model is slow to calculate, the fix is usually in the model rather than in the automation, and the automation's job is to avoid triggering it repeatedly.

Conclusion

Refreshing is easy; knowing when it is finished is the actual work. Prefer synchronous refreshes by turning BackgroundQuery off, fall back to CalculateUntilAsyncQueriesDone when you cannot, recalculate with CalculateFullRebuild after writing formulas, and refresh pivot caches before the tables that read them. Then verify — a timestamp on the sheet and a comparison against a known figure turn the silent failure mode into a visible one.

Frequently asked questions

Does RefreshAll wait for the refresh to finish? No. It starts every connection and returns immediately, which is why saving straight afterwards can write the previous run's numbers. Follow it with CalculateUntilAsyncQueriesDone, or poll the connection's Refreshing property.

Why do my Power Query connections not refresh? Usually because BackgroundQuery is left on. Set that flag to False on the connection's OLEDBConnection object before refreshing, and the call becomes synchronous — which removes the timing question entirely.

What is the difference between Calculate and CalculateFullRebuild? Calculate recalculates what Excel thinks is dirty. CalculateFull recalculates everything regardless. CalculateFullRebuild also rebuilds the dependency tree, which is the one to use when formulas were added by a script and Excel's dependency graph may be stale.

Can I refresh a pivot table without refreshing its source query? Yes — call RefreshTable on the PivotTable, which rebuilds it from its cache. To update the cache from the underlying data, refresh the PivotCache or the connection instead.

Related

• Up one level: Automating Excel with COM and pywin32 — the session skeleton these calls run inside.
• Refresh Pivot Tables and Queries in Excel with Python — the same operations through xlwings.
• Refresh an Excel Report from a Database on a Schedule — replacing the workbook connection with a Python query.
• Recalculate Excel Formulas Without Excel in Python — what is possible when no copy of Excel is available.
• Validate an Excel Report Before Sending It — the checks that catch a stale refresh before a recipient does.