Work with Macro-Enabled .xlsm Files in openpyxl

Macro-enabled workbooks are common in finance and operations: a template with buttons, a refresh routine, some validation VBA — and a monthly data drop that somebody currently pastes in by hand. Automating that paste with openpyxl works, but there is one trap that catches nearly everyone the first time. Load the file, change a cell, save it, and the macros are gone. No exception, no warning, just a workbook whose buttons no longer do anything. This guide shows the two-part rule that prevents it, what openpyxl still cannot preserve, and how to verify before you ship. It is part of Handling Excel File Formats and Conversions.

Prerequisites

pip install openpyxl

You also need a macro-enabled workbook. To make one for testing, open Excel, record any trivial macro (Developer → Record Macro), and save as "Excel Macro-Enabled Workbook (*.xlsm)". You cannot create the VBA project from Python — openpyxl can carry one but not author one.

Step 1 — The two-part rule

Both halves are required. Missing either one loses the macros:

from openpyxl import load_workbook

# 1. keep_vba=True — hold the vbaProject.bin part in memory.
wb = load_workbook("template.xlsm", keep_vba=True)

ws = wb["Data"]
ws["B2"] = 4821
ws["B3"] = "2026-08-15"

# 2. Save to an .xlsm path. An .xlsx path drops the VBA even with keep_vba.
wb.save("template_filled.xlsm")

The reason the filename matters is structural, not stylistic. openpyxl picks the output content type from the extension, and the .xlsx content type has no slot for a VBA part — so the part is simply not written. There is no error because, from the format's point of view, nothing is wrong.

Guard against it in code rather than trusting yourself to remember:

from pathlib import Path
from openpyxl import load_workbook

def edit_macro_workbook(src, dest, mutate):
    """Open a macro-enabled workbook, apply `mutate(wb)`, save it back safely."""
    src, dest = Path(src), Path(dest)
    if src.suffix.lower() != ".xlsm":
        raise ValueError(f"{src.name} is not macro-enabled")
    if dest.suffix.lower() != ".xlsm":
        raise ValueError(
            f"Refusing to write {dest.name}: saving a macro workbook to "
            f"'{dest.suffix}' would discard the VBA project."
        )

    wb = load_workbook(src, keep_vba=True)
    mutate(wb)
    wb.save(dest)
    return dest

edit_macro_workbook(
    "template.xlsm",
    "august.xlsm",
    lambda wb: wb["Data"].cell(row=2, column=2, value=4821),
)

Step 2 — Know what openpyxl does not preserve

keep_vba covers the VBA project. It does not make openpyxl a lossless round-tripper for everything else. Anything openpyxl cannot model is rebuilt from its own understanding of the file, and what it does not understand is dropped.

That third and fourth row are the ones that surprise people: a template with a "Refresh" button loses the button while keeping the macro it called. If your template has controls or charts, the safe pattern is to treat the workbook as fill-only — write values into cells and never restructure — which is exactly the discipline described in populating an Excel template without losing formatting.

Step 3 — Write through defined names, not coordinates

Macros usually address ranges by name, not by cell reference, precisely so a template can be reorganised without breaking. Writing through the same names makes your Python code equally robust — and it fails loudly if somebody removes one.

from openpyxl import load_workbook
from openpyxl.utils import range_boundaries

def write_named(wb, name, value):
    """Set the value of a single-cell defined name."""
    if name not in wb.defined_names:
        raise KeyError(f"Defined name '{name}' is missing from the template")

    destinations = list(wb.defined_names[name].destinations)
    if len(destinations) != 1:
        raise ValueError(f"'{name}' does not resolve to one range")

    sheet_title, ref = destinations[0]
    min_col, min_row, *_ = range_boundaries(ref.replace("$", ""))
    wb[sheet_title].cell(row=min_row, column=min_col, value=value)

wb = load_workbook("template.xlsm", keep_vba=True)
write_named(wb, "ReportMonth", "2026-08")
write_named(wb, "TotalUnits", 4821)
wb.save("august.xlsm")

Creating and inspecting names is covered in creating a named range in Excel with openpyxl.

Step 4 — Verify the macros survived

Do not ship on faith. Because .xlsm is a zip, you can confirm the VBA part is present in two lines — no Excel required, which means this check runs in CI on Linux:

import zipfile

def has_macros(path):
    """True if the workbook still contains a VBA project."""
    with zipfile.ZipFile(path) as zf:
        return "xl/vbaProject.bin" in zf.namelist()

assert has_macros("template.xlsm"), "source has no macros — check the fixture"
assert has_macros("august.xlsm"), "macros were dropped during the write"
print("VBA project intact")

A stricter version compares the bytes, catching a corrupted carry-through as well as a missing one:

import zipfile, hashlib

def vba_digest(path):
    with zipfile.ZipFile(path) as zf:
        if "xl/vbaProject.bin" not in zf.namelist():
            return None
        return hashlib.sha256(zf.read("xl/vbaProject.bin")).hexdigest()

assert vba_digest("template.xlsm") == vba_digest("august.xlsm"), \
    "the VBA project changed during the round trip"

Wire that into a test alongside the output assertions in testing Excel output with pytest and a regression can never reach users silently.

Common pitfalls and fixes

Performance and scale notes

keep_vba=True costs almost nothing at run time. The VBA part is read as bytes and written back as bytes with no parsing, so the overhead is roughly the size of vbaProject.bin in memory — typically tens of kilobytes even for substantial macro projects.

The real cost is that macro workbooks force you into openpyxl's normal mode. The write_only streaming mode, which is what makes writing large DataFrames memory-efficient, cannot carry a VBA project — it builds a new workbook from nothing. So a macro template holding hundreds of thousands of rows will be memory-hungry to edit.

The way out is to split the roles. Keep the macro workbook small — the template, the controls, the summary sheet — and put bulk data in a separate .xlsx the template links to or the macro imports:

import pandas as pd
from openpyxl import load_workbook

# Bulk data goes to a plain .xlsx, written the fast way.
big = pd.DataFrame({"id": range(500_000), "amount": range(500_000)})
big.to_excel("detail.xlsx", index=False, engine="xlsxwriter")

# The macro template only receives the summary figures.
wb = load_workbook("dashboard.xlsm", keep_vba=True)
ws = wb["Summary"]
ws["B2"] = len(big)
ws["B3"] = float(big["amount"].sum())
wb.save("dashboard_august.xlsm")

That split keeps the memory profile flat and keeps the fragile, hand-built part of the workbook untouched by the bulk write.

Conclusion

Editing .xlsm files from Python is safe once you internalise the two-part rule: keep_vba=True on load, an .xlsm extension on save. Beyond that, treat the workbook as something you fill, not something you restructure — openpyxl silently drops form controls and pre-existing charts, and VBA that names a sheet you renamed will fail at run time inside Excel. Write through defined names where you can, check for xl/vbaProject.bin in the output before shipping, and keep bulk data out of the macro workbook entirely.

Frequently asked questions

Does keep_vba=True let me read or edit the macro code? No. openpyxl treats vbaProject.bin as an opaque blob — it carries the bytes through unchanged but cannot parse, read or modify the VBA source. To change macro code you need Excel itself, or a dedicated VBA tooling library.

I set keep_vba=True and the macros are still gone. Why? Almost always the output filename. Saving to a .xlsx path discards the VBA project regardless of keep_vba, because .xlsx has no place to store it. Save to a .xlsm path.

Can openpyxl run a macro? No. openpyxl only reads and writes files; it never starts Excel. To execute a macro you need xlwings driving a real Excel instance on Windows or macOS.

Will Excel warn users about the file my script produced? Yes, the usual macro security prompt appears, exactly as it would for the original. If the workbook was digitally signed, the signature will no longer match after any edit and the file will be treated as unsigned.

Is it safe to run this on a server? Yes. openpyxl never executes the VBA, so processing an untrusted .xlsm carries no more risk than processing an .xlsx. The macros only run when someone opens the file in Excel and allows them.

Related

• Up to the parent: Handling Excel File Formats and Conversions — where .xlsm sits among the other formats.
• Populate an Excel Template Without Losing Formatting — the fill-only discipline this guide recommends.
• xlwings: Run a Macro from Python — actually executing the VBA you preserved.
• Create a Named Range in Excel with openpyxl — the names to write through.
• Test Excel Output with pytest — where the macro-survival assertion belongs.