Read an Excel File That Is Open in Excel

Shared workbooks are open in Excel most of the working day, and the scripts that read them run during the working day too. That raises three practical questions. Can Python read a file while Excel has it open? Usually, yes — but what it reads is the last saved version, not the figures on the user's screen. What happens if the read coincides with a save? It can fail with PermissionError or with a BadZipFile error from a half-written file. And what if the script needs the values the user is looking at right now, including changes they have not saved? That needs a different tool altogether. This page covers all three: reading a consistent snapshot of the saved file with retries, telling whether a workbook is open, and reading live values from the running Excel with xlwings. It belongs to Reading Excel Files with pandas.

Prerequisites

pip install pandas openpyxl
pip install xlwings          # only for reading live values, Windows or macOS with Excel

The snapshot and retry code runs anywhere; the sample run simulates a lock and a half-saved file. Reading live values needs desktop Excel and is shown as it runs there.

Step 1 — What you read is the last save

Excel works on a copy of the workbook in memory and writes the file only when the user saves — or, with AutoSave on a OneDrive or SharePoint file, every few seconds. Between saves, the file on disk and the screen differ. pandas, openpyxl and every other file-based library read the file on disk:

For a scheduled report that is usually what you want: the saved file is the version the owner has committed to. When it is not — a dashboard that should show today's edits — ask whether the workbook can be saved before the run, switch on AutoSave for it, or read it live as in Step 4. On Windows, Excel normally lets other programs read an open workbook; writing to it is what fails, as covered in Handle Permission Denied When Writing Excel in Python.

Step 2 — Read a snapshot, and retry when the timing is bad

Two things can still go wrong with a read. On some network shares and synced folders the open file cannot be opened at all and the read raises PermissionError. And if Excel is in the middle of writing the file, the read can see an incomplete zip archive and raise BadZipFile. Copy the file to a private location first, check the copy is a complete workbook, and retry a few times with a pause:

import shutil, tempfile, time, zipfile
from pathlib import Path
import pandas as pd

def read_snapshot(path, attempts=4, wait=0.5, **read_kwargs):
    path = Path(path)
    for attempt in range(1, attempts + 1):
        try:
            with tempfile.TemporaryDirectory() as tmp:
                copy = Path(tmp) / path.name
                shutil.copy2(path, copy)
                with zipfile.ZipFile(copy) as z:
                    if z.testzip() is not None:
                        raise zipfile.BadZipFile("corrupt member")
                return pd.read_excel(copy, **read_kwargs), attempt
        except (PermissionError, zipfile.BadZipFile) as e:
            print(f"  attempt {attempt}: {type(e).__name__} — retrying in {wait:.1f}s")
            time.sleep(wait)
            wait *= 2
    raise RuntimeError(f"could not read a complete copy of {path} after {attempts} attempts")

df, attempt = read_snapshot("tracker.xlsx")
print(df.to_string(index=False), "| read on attempt", attempt)

Reading from a copy has a second benefit: the script holds the original open only for the instant of the copy, so it never gets in the way of the user saving. testzip() reads every member of the archive and reports the first damaged one, which catches a half-written file before pandas produces a more confusing error. Doubling the wait between attempts gives a slow save on a network drive time to finish. The temporary directory is removed when the with block ends, so snapshots never accumulate, and because the read happens inside the block the DataFrame is complete before the copy disappears. For very large workbooks the copy costs a moment of disk time, which is small next to parsing the file; if it matters, copy once and read several sheets from the same snapshot rather than copying per sheet.

Step 3 — Simulate the bad timing

Test the retry path without Excel by making the file look mid-save for the first attempt and complete afterwards:

import threading

good = Path("tracker.xlsx").read_bytes()
Path("tracker.xlsx").write_bytes(good[: len(good) // 2])          # a save in progress
threading.Timer(0.3, lambda: Path("tracker.xlsx").write_bytes(good)).start()   # the save finishes
df, attempt = read_snapshot("tracker.xlsx", wait=0.4)
print("recovered on attempt", attempt, "with", len(df), "rows")

The first attempt finds half a workbook and raises BadZipFile; by the second attempt the "save" has finished and the snapshot reads all three projects. If every attempt fails the function raises a clear error naming the file, which a scheduled job can log and alert on. Whether a workbook is open at all — Excel's ~$ owner file next to it, LibreOffice's .~lock file — can be checked first, as shown in Ignore Excel Lock Files When Processing a Folder; a job might log "read while open by another user" so that stale figures can be explained later.

Step 4 — Read the live values with xlwings

When the script must see what is on the user's screen, read through Excel itself. xlwings attaches to a workbook that is already open in the running Excel instead of opening the file again:

import xlwings as xw

book = xw.books["tracker.xlsx"]                   # the workbook as currently open in Excel
sheet = book.sheets[0]
live = sheet["A1"].options(pd.DataFrame, expand="table", index=False).value
saved = pd.read_excel(book.fullname)
changed = live.compare(saved) if live.shape == saved.shape else "shape differs"

xw.books lists the workbooks open in the active Excel instance, by file name; reading a range from one returns the current cell values, including edits the user has not saved and formulas Excel has just recalculated. Comparing with a read of the saved file shows exactly what the user has changed since their last save. The limits are practical: this needs Excel running on the same machine, it reads a single user's session — not other people's open copies on other machines — and it should be read-only, because writing into a workbook a person is actively editing risks overwriting their work. More on xlwings in Read and Write a Live Excel Workbook with xlwings.

Reduce the problem at the source

Reading around people's editing sessions works, but the cleaner fix is often organisational. If a script reads a workbook that people edit all day, agree a moment when it is "published": the owner saves, or a macro saves a copy to a published folder, and the script reads only that copy. For workbooks in OneDrive or SharePoint, reading through Microsoft Graph returns the version the service holds — with AutoSave, close to what users see — without touching anyone's desktop session, as described in Read an Excel File from SharePoint or OneDrive into pandas. And if the script needs only a few figures, keeping them in a small, separate input workbook that is edited rarely removes most of the contention.

Common pitfalls

• Expecting unsaved edits in pandas. File-based reads see the last save only.
• Reading the original during a save. Copy, check with testzip(), then read.
• Retrying forever. Limit attempts and fail with a clear message.
• Writing to a workbook someone has open. Read live values, but leave the writing to them.
• Reading ~$ files. They are lock files, not workbooks.

Conclusion

A workbook open in Excel can usually be read with pandas, but what pandas reads is the last saved version. Read a snapshot — copy, check the copy with testzip(), then read_excel — and retry with a growing delay on PermissionError or BadZipFile, failing clearly after a few attempts. When the current, unsaved values are needed, attach to the open workbook with xlwings and read from Excel itself.

Frequently asked questions

Can pandas read an Excel file while it is open in Excel? Usually yes, on Windows and macOS; pandas reads the last saved version of the file, not unsaved changes.

Why do I get PermissionError when reading an open Excel file? Some file shares and synced folders do not allow reading a file another program has open; copy the file first and retry with a short delay.

How do I read the values a user has not saved yet? Attach to the open workbook in the running Excel with xlwings, for example xw.books"file.xlsx", and read the range from there.

How can I tell whether a workbook is open in Excel? Look for Excel's ~$ owner file or LibreOffice's .~lock file in the same folder as the workbook.

Related

• Up one level: Reading Excel Files with pandas — reading workbooks into DataFrames.
• Ignore Excel Lock Files When Processing a Folder — lock files in folder loops.
• Fix BadZipFile Error When Reading Excel in Python — the error a half-saved file gives.
• Write Excel Files Atomically in Python — avoiding half-written files in your own jobs.