Python Doc & Data Automation

Fix PermissionError Writing Excel File

The nightly report job fails at the final step:

PermissionError: [Errno 13] Permission denied: 'P:/Finance/Reports/daily-sales.xlsx'

On Windows the same situation can surface as PermissionError: [WinError 32] The process cannot access the file because it is being used by another process. The path is correct, the folder is writable, and running the script again ten minutes later works — because by then whoever had the workbook open in Excel has closed it.

Root Cause

Excel opens workbooks with a deny-write share mode and creates a hidden lock file named ~$daily-sales.xlsx beside them. While that lock exists, any other process — including Python — is refused write access, and the operating system reports a permission error rather than a "file in use" error. Three other causes produce the identical message. The destination path may be a directory rather than a file, which raises IsADirectoryError on Linux but a plain PermissionError on Windows. The file may be read-only, either by its attributes or because the user has read permission on a network share but not write. And on managed shares, antivirus or backup agents briefly hold files open after they are written, so a job that writes and immediately re-opens the same file can fail intermittently for a few hundred milliseconds. Whatever the cause, the failure happens after the report has been computed, so the work is lost unless the job is written to survive it.

Minimal Diagnostic

Check every reason before writing: the path type, the lock file, file attributes, effective permissions and whether the file can actually be opened for writing right now.

# stdlib only
import os
import stat
from pathlib import Path

DEST = Path("P:/Finance/Reports/daily-sales.xlsx")

def diagnose(dest: Path) -> None:
    print(f"path: {dest}")
    print(f"  parent exists: {dest.parent.is_dir()}  parent writable: {os.access(dest.parent, os.W_OK)}")
    if dest.exists():
        mode = dest.stat().st_mode
        print(f"  exists: file={dest.is_file()} dir={dest.is_dir()} size={dest.stat().st_size}")
        print(f"  read-only attribute: {not bool(mode & stat.S_IWUSR)}  os.access W_OK: {os.access(dest, os.W_OK)}")
    lock = dest.with_name("~$" + dest.name)
    print(f"  Excel lock file present: {lock.exists()}" + (f" ({lock})" if lock.exists() else ""))
    try:
        with dest.open("ab"):                       # open for append: needs write access, changes nothing
            print("  can open for writing: yes")
    except FileNotFoundError:
        print("  can open for writing: file does not exist yet (fine)")
    except PermissionError as exc:
        print(f"  can open for writing: NO -> {exc}")

if __name__ == "__main__":
    diagnose(DEST)

path: P:/Finance/Reports/daily-sales.xlsx
  parent exists: True  parent writable: True
  exists: file=True dir=False size=248320
  read-only attribute: False  os.access W_OK: True
  Excel lock file present: True (P:/Finance/Reports/~$daily-sales.xlsx)
  can open for writing: NO -> [Errno 13] Permission denied

os.access reports write permission because the permissions allow it; the lock is a share-mode conflict, which only an actual open attempt reveals. The lock file names the culprit: someone has the workbook open.

Which permission error you have The root asks what the diagnostic showed. A tilde dollar lock file next to the workbook means it is open in Excel, so the job should write to a timestamped alternative or retry. A read-only attribute or no write permission means a file system or share problem for an administrator. A path that is a directory means the destination is wrong. No lock file and no attribute problem, with the error disappearing on retry, points at antivirus or backup agents holding the file briefly. What did the diagnostic report? lock file, attributes, path type ~$ lock present Open in Excel write elsewhere or retry read-only / no W_OK Permissions share or file attributes path is a directory Wrong destination include the file name intermittent only Antivirus or backup retry with backoff

Fix: Write Atomically, Retry, and Fall Back to a Dated File

Compute the report once, write it to a temporary file in the same folder, then replace the destination. If the destination is locked, retry briefly, and if it stays locked, save under a dated name so the work is never lost. Changed lines carry comments.

# pip install "pandas>=2.2" openpyxl
import logging
import os
import time
from datetime import datetime
from pathlib import Path
import pandas as pd

log = logging.getLogger("report")

def write_excel_safely(frames: dict[str, pd.DataFrame], dest: Path,
                       attempts: int = 5, wait: float = 3.0) -> Path:
    dest.parent.mkdir(parents=True, exist_ok=True)
    if dest.is_dir():
        raise IsADirectoryError(f"{dest} is a directory; include the file name")     # changed: clear message
    tmp = dest.with_name(f".{dest.stem}.{os.getpid()}.tmp.xlsx")                     # changed: same folder
    with pd.ExcelWriter(tmp, engine="openpyxl") as writer:                           # changed: write once
        for sheet, frame in frames.items():
            frame.to_excel(writer, sheet_name=sheet[:31], index=False)
    for attempt in range(1, attempts + 1):
        try:
            os.replace(tmp, dest)                                                    # changed: atomic swap
            return dest
        except PermissionError as exc:
            if attempt == attempts:
                fallback = dest.with_name(f"{dest.stem}-{datetime.now():%Y%m%d-%H%M}{dest.suffix}")
                os.replace(tmp, fallback)                                            # changed: never lose work
                log.warning("%s locked (%s); wrote %s instead", dest.name, exc.errno, fallback.name)
                return fallback
            log.info("%s locked, retrying in %.0fs (%d/%d)", dest.name, wait, attempt, attempts)
            time.sleep(wait)
    raise RuntimeError("unreachable")

if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO, format="%(levelname)s %(message)s")
    data = {"Sales": pd.DataFrame({"region": ["EMEA"], "revenue": [1240.5]})}
    print(write_excel_safely(data, Path("out/daily-sales.xlsx")))

Writing the temporary file in the destination folder matters twice: os.replace is atomic only within one file system, and a share that refuses writes fails at the temporary write — before the report is computed into a half-written destination. The dotted, process-id-suffixed temporary name keeps two concurrent runs from colliding and hides the file from Explorer's default view. A dated fallback turns "the job failed" into "the job produced a file with a different name", which someone can act on the next morning.

Safe write sequence The job first checks that the destination is a file path and its folder exists. It writes the workbook to a hidden temporary file in the same folder. It then attempts os.replace, retrying up to five times with a pause when a PermissionError indicates the workbook is open. If it is still locked after the last attempt, the temporary file is renamed to a dated file name so the report is preserved and a warning is logged. Validate dest file not directory Write temp same folder os.replace atomic Retry x5 3 s apart Dated fallback work preserved

Variant Fix 1: Tell the User Who Has It Open

On Windows shares, the Excel lock file records the user name who opened the workbook, which turns a cryptic failure into an actionable message:

# stdlib only
from pathlib import Path

def lock_owner(dest: Path) -> str | None:
    lock = dest.with_name("~$" + dest.name)
    if not lock.exists():
        return None
    try:
        raw = lock.read_bytes()
    except OSError:
        return "unknown (lock file not readable)"
    # Excel stores a length byte followed by the user name, usually UTF-16LE for .xlsx
    length = raw[0]
    name = raw[1:1 + length * 2].decode("utf-16-le", errors="ignore").strip("\x00 ").strip()
    return name or "unknown"

The exact layout varies between Excel versions, so treat the result as a hint and never as an assertion — but "daily-sales.xlsx is open by J. Patel" in a log or alert saves an hour of investigation. Include it in the warning message from the fix, and in the failure alert described in add retries and failure alerts to automation jobs.

Variant Fix 2: Stop Writing to Files People Open

The reliable long-term fix is to remove the conflict. Publish reports as new, dated files and keep a stable "latest" pointer, so readers always open a file the job is not writing:

# pip install "pandas>=2.2" openpyxl
import os
from datetime import date
from pathlib import Path

def publish_report(frames: dict, folder: Path, base: str = "daily-sales") -> tuple[Path, Path]:
    folder.mkdir(parents=True, exist_ok=True)
    dated = folder / f"{base}-{date.today():%Y-%m-%d}.xlsx"
    write_excel_safely(frames, dated)                       # dated files are never open when written
    latest = folder / f"{base}-latest.xlsx"
    tmp = folder / f".{base}-latest.tmp.xlsx"
    tmp.write_bytes(dated.read_bytes())
    try:
        os.replace(tmp, latest)                             # replaced only if not locked
    except PermissionError:
        tmp.unlink(missing_ok=True)                         # someone has 'latest' open; dated file is enough
    return dated, latest

Readers who want the newest data open -latest; anyone who needs a specific day opens the dated file. If -latest is locked, the dated file still exists and the job succeeds. For teams that live in the workbook all day, the better answer is a read-only copy or a dashboard fed from a database — writing reports into a file that people keep open will always be a race.

Strategies when the destination is locked Failing fast loses the computed report and needs a rerun, but is simple. Retrying briefly recovers when a colleague closes the workbook within seconds. A dated fallback always preserves the work and needs someone to notice the different name. Publishing dated files with a latest pointer avoids the conflict entirely and is the most reliable for daily reports. Strategy Work preserved Reader experience Effort Fail fast lost no report none Retry briefly sometimes same file small Dated fallback always different name small Dated + latest pointer always always readable restructure

Reading a Locked Workbook

Reading can hit the same error when a template or an input workbook is open. Reading does not need write access, but Excel's share mode can still block it on Windows; copying the bytes first avoids the conflict:

# pip install "pandas>=2.2" openpyxl
import shutil
import tempfile
from pathlib import Path
import pandas as pd

def read_excel_even_if_open(path: Path, **kwargs) -> dict[str, pd.DataFrame]:
    try:
        return pd.read_excel(path, sheet_name=None, **kwargs)
    except PermissionError:
        with tempfile.TemporaryDirectory() as tmp:
            copy = Path(tmp) / path.name
            shutil.copy2(path, copy)                      # copying is usually permitted while Excel holds it
            return pd.read_excel(copy, sheet_name=None, **kwargs)

The copy reflects the last saved state, not the unsaved edits in the open window — which is the correct input for an automated job anyway. If even the copy fails, the file is genuinely not readable by this user, which is a permissions question for whoever owns the share.

Verification

Prove the job behaves correctly while the destination is locked, without needing Excel: hold the file open with a deny-write share mode on Windows, or make it read-only on Linux.

# pip install "pandas>=2.2" openpyxl
import os
import stat
import tempfile
from pathlib import Path
import pandas as pd

def test_locked_destination() -> None:
    with tempfile.TemporaryDirectory() as tmp:
        dest = Path(tmp) / "report.xlsx"
        frames = {"Data": pd.DataFrame({"a": [1, 2]})}
        assert write_excel_safely(frames, dest) == dest, "first write should use the real name"
        dest.chmod(stat.S_IRUSR)                                   # simulate a locked/read-only destination
        os.chmod(dest.parent, stat.S_IRWXU)
        try:
            result = write_excel_safely(frames, dest, attempts=2, wait=0.1)
        finally:
            dest.chmod(stat.S_IRUSR | stat.S_IWUSR)
        assert result != dest and result.exists(), "fallback file not written"
        assert not list(dest.parent.glob(".*tmp.xlsx")), "temporary file left behind"
        print(f"locked-destination behaviour verified: fell back to {result.name}")

if __name__ == "__main__":
    test_locked_destination()

On Linux a read-only file can still be replaced if the directory is writable, so this test asserts the fallback path only where the platform actually refuses; run the equivalent check on Windows with a real Excel window open before trusting the job in production.

FAQ

Can I delete the ~$ lock file to force the write? No. The file is a side effect; the real lock is the open handle. Deleting it risks data loss for the person editing.

Why does os.access(path, os.W_OK) say writable? It checks permissions, not share modes or existing handles. Only an actual open attempt is conclusive on Windows.

Does writing with xlsxwriter avoid the problem? No — the conflict is at the file system level, not in the library. Any writer fails the same way.

Should the job email the report instead of writing to a share? For reports people only read, yes; it removes the conflict entirely. See email generated reports with Python.

Part of Automating Excel Report Generation.