Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Refresh Excel Pivot Tables and Queries with Python

Recalculate a workbook, refresh pivot caches and Power Query connections, and save the result — with xlwings or pywin32 on Windows, plus the headless alternatives.

Some workbooks cannot be finished by a library. A file whose numbers come from pivot caches, Power Query connections or volatile formulas needs Excel's own calculation engine to produce them, and no pure-Python package computes those. When the file must arrive with fresh values, the answer is to drive a real Excel instance — with xlwings or pywin32 — refresh it, and save. This guide does that reliably, and then covers the headless alternative for the servers where Excel does not exist. It extends Automating Excel with xlwings Basics.

What each tool can and cannot compute openpyxl reads and writes the file's stored values and definitions, while only a running Excel recalculates formulas, pivot caches and query connections. a Python library alone Excel driven from Python read and write cell values preserve a pivot definition cannot recalculate anything recalculates every formula rebuilds pivot caches needs Excel and a desktop session Choose the left column unless the workbook genuinely needs Excel's engine

Prerequisites

On Windows with Excel installed:

Bash
pip install xlwings pandas

xlwings wraps the COM interface and works on Windows and macOS. pywin32 is the lower-level alternative on Windows and exposes the same object model with more verbosity.

Refresh everything and save

The whole job is four lines of intent — open, refresh, calculate, save — plus the care that makes it reliable:

Python
"""refresh.py — open a workbook, refresh it, save, quit."""
from pathlib import Path

import xlwings as xw

path = Path("dashboard.xlsx").resolve()

with xw.App(visible=False, add_book=False) as app:
    app.display_alerts = False
    app.screen_updating = False

    book = app.books.open(str(path), update_links=False)
    try:
        book.api.RefreshAll()          # pivot caches and query connections
        app.api.CalculateUntilAsyncQueriesDone()
        book.save()
    finally:
        book.close()

Three settings prevent the classic hang. display_alerts = False stops a modal dialog — "a file is already open", "do you want to update links" — waiting forever for a click nobody will give it. visible=False keeps the window off the screen. And CalculateUntilAsyncQueriesDone() is what actually waits: RefreshAll returns immediately for anything running in the background, so saving straight after it writes the old values.

Make the refresh synchronous

The most reliable approach is to turn background refresh off per connection, so RefreshAll cannot return early:

Python
import xlwings as xw

with xw.App(visible=False, add_book=False) as app:
    app.display_alerts = False
    book = app.books.open("dashboard.xlsx")
    try:
        for connection in book.api.Connections:
            try:
                connection.OLEDBConnection.BackgroundQuery = False
            except Exception:            # not every connection type has one
                pass
        book.api.RefreshAll()
        app.api.CalculateFullRebuild()
        book.save()
    finally:
        book.close()

CalculateFullRebuild() is the heaviest recalculation Excel offers — it rebuilds the dependency tree as well as recomputing — which is what you want in an unattended job where a stale cached value would go unnoticed.

Refresh one pivot table at a time

When only part of the workbook needs updating, address the pivot caches directly:

Python
import xlwings as xw

with xw.App(visible=False, add_book=False) as app:
    app.display_alerts = False
    book = app.books.open("dashboard.xlsx")
    try:
        for cache in book.api.PivotCaches():
            cache.Refresh()

        sheet = book.sheets["Summary"]
        for pivot in sheet.api.PivotTables():
            print("refreshed:", pivot.Name)
            pivot.RefreshTable()
        book.save()
    finally:
        book.close()

Refreshing the cache updates the data behind every pivot that shares it; RefreshTable() re-lays out one pivot. Doing both is belt and braces, and cheap compared with opening Excel in the first place.

The order a reliable refresh has to follow Alerts are disabled, background query is turned off, the workbook is refreshed, calculation is forced to completion, and only then is the file saved and Excel closed. Save too early and you ship yesterday's numbers 1. silence alerts off 2. synchronise background off 3. RefreshAll caches, queries 4. calculate until done 5. save, close the book, quit the app in a finally block, always

The pywin32 equivalent

If you would rather not add xlwings, the same operations are available directly through COM:

Python
import win32com.client as win32

excel = win32.DispatchEx("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False
try:
    book = excel.Workbooks.Open(r"C:\reports\dashboard.xlsx", UpdateLinks=0)
    book.RefreshAll()
    excel.CalculateUntilAsyncQueriesDone()
    book.Save()
    book.Close(SaveChanges=True)
finally:
    excel.Quit()

DispatchEx starts a fresh Excel process rather than attaching to one the user already has open — important in automation, where attaching to a colleague's session means their unsaved work and their modal dialogs become your problem.

Clean up, or accumulate orphan processes

The most common operational failure of Excel automation is a pile of invisible EXCEL.EXE processes, each holding a file lock. Two habits prevent it: always quit in a finally block, and sweep before you start:

Python
"""Kill orphaned Excel processes left by earlier failures."""
import psutil

for proc in psutil.process_iter(["name"]):
    if proc.info["name"] and proc.info["name"].lower() == "excel.exe":
        proc.kill()

Only do that on a dedicated automation machine — killing Excel on a shared desktop destroys someone's unsaved work. On a machine where a person also works, prefer detecting the orphan and failing loudly.

The headless alternative

If the workbook exists to present an aggregation, compute the aggregation in Python and write the values. Then nothing needs refreshing, and the report runs on any server:

Python
import pandas as pd

df = pd.read_excel("raw_data.xlsx", engine="calamine")
summary = (df.groupby(["region", "product"], as_index=False)["revenue"]
             .sum()
             .sort_values("revenue", ascending=False))

with pd.ExcelWriter("dashboard.xlsx", engine="xlsxwriter") as writer:
    summary.to_excel(writer, index=False, sheet_name="Summary")
    df.to_excel(writer, index=False, sheet_name="Data")

This is the right default. A pivot table is a presentation of a group-by; if the group-by happens in pandas, the workbook holds finished numbers that open correctly everywhere — no Excel licence, no desktop session, no COM. The techniques are in Create a pivot table from Excel with pandas, and if a genuine Excel pivot object is required, Add a native Excel pivot table with Python shows how to define one that refreshes on open.

Make a workbook refresh itself when opened

Between the two extremes there is a middle path: mark the pivot cache to refresh when the file is opened, so the recipient's own Excel does the work:

Python
from openpyxl import load_workbook

wb = load_workbook("dashboard.xlsx")
for cache in wb._pivots if hasattr(wb, "_pivots") else []:
    cache.cache.refreshOnLoad = True
wb.save("dashboard.xlsx")

The values in the file are still stale, but they update the moment a person opens it. That is often enough for a distributed report, and it costs no server-side Excel at all.

Decide which route your workbook needs

Three approaches, and the choice is made by what the file actually contains rather than by preference:

Choosing between driving Excel, refreshing on open, and computing headlessly Driving Excel gives fresh values but needs a licensed desktop, refresh-on-open pushes the work to the reader, and computing in pandas removes the need to refresh at all. What the workbook contains decides the route drive Excel values fresh on delivery needs Excel + a session slow, serialised for: legacy dashboards refresh on open reader's Excel does it stale until opened no server-side Excel for: distributed files compute in pandas values written directly runs anywhere, fast rebuilds the logic once for: everything new

If you are inheriting a workbook somebody built by hand, driving Excel buys time while you port the logic. If you are building something new, port the logic first — the automation you do not have to operate is always the cheapest one.

Common pitfalls and gotchas

  • Saving before the refresh completes. RefreshAll is asynchronous for background queries; wait explicitly.
  • A modal dialog with nobody to click it. Set DisplayAlerts = False and UpdateLinks=0 on open.
  • Running under a service account with no desktop. Excel COM needs an interactive session; a scheduled task set to "run whether user is logged on or not" usually fails silently.
  • Relative paths. COM resolves paths against Excel's working directory, not your script's. Always pass an absolute path.
  • Leaving Excel running. Quit in a finally, and check the process list after a failed run.

Performance and scale notes

Starting Excel costs seconds, not milliseconds, and a full rebuild of a large workbook can take minutes. That makes this a batch operation, never a request-time one: refresh on a schedule and serve the resulting file, rather than refreshing when someone asks. Excel is also single-instance-per-session in practice, so refreshes serialise — one machine cannot process ten workbooks in parallel the way a pandas job can. If throughput matters, the headless route is not just more portable but genuinely faster, because it skips the application entirely. Scheduling either approach is covered in Run a Python Excel script on Windows Task Scheduler.

Conclusion

Only Excel can recalculate Excel. When a workbook depends on pivot caches, query connections or volatile formulas, drive a hidden instance with xlwings or pywin32: silence the alerts, make the refresh synchronous, force a full calculation, save, and quit in a finally. But treat that as the exception — computing the aggregation in pandas and writing finished values produces a workbook that needs no refresh, runs on any server, and finishes in a fraction of the time.

Frequently asked questions

Can openpyxl refresh a pivot table? No. openpyxl can preserve a pivot table's definition and set it to refresh when the file is opened, but it cannot recalculate the cache — only Excel itself computes pivot results and formula values.

Do I need Excel installed? For a genuine refresh, yes. xlwings and pywin32 both drive a real Excel application through COM, which means Windows (or macOS for xlwings) with Excel present. On Linux there is no Excel to drive.

How do I know when RefreshAll has finished?RefreshAll returns immediately for background queries. Set each connection's BackgroundQuery to False before refreshing, or call CalculateUntilAsyncQueriesDone(), then save.

What is the headless alternative? Compute the aggregation in pandas and write the values, so no recalculation is needed. A summary built from data rather than from a cache opens correctly anywhere, including on a Linux server.

Why does my scheduled task do nothing when nobody is logged in? Excel automation needs an interactive desktop session. A task set to run whether or not the user is logged on typically fails silently — this is the strongest argument for the headless approach.