How-to

Getting Started With Python Excel AutomationDeep dive

Add a VBA Macro to a Workbook with xlsxwriter

Ship a macro-enabled .xlsm report from Python: extract a vbaProject.bin once from a workbook saved in Excel, attach it to every new file with xlsxwriter, wire a button to a macro, keep the workbook and sheet code names consistent, and check the result without opening Excel.

Macros & VBA xlsxwriter

Some reports still need a macro: a button that refreshes a pivot, a Workbook_Open handler that selects the right sheet, a small routine the finance team runs every month. When the report itself is generated by Python, the macro has to travel inside the file Python writes. xlsxwriter cannot compile VBA, but it can embed a VBA project that Excel compiled, which is enough: write the macros once in Excel, extract the binary, and attach it to every workbook Python generates. This page is part of Automating Excel with COM and pywin32.

Prerequisites

Bash

pip install xlsxwriter oletools openpyxl

template/macros_template.xlsm stands in for a workbook someone saved in Excel after writing the macros in the VBA editor (Alt+F11). Its project holds one standard module, Module1, with a procedure named say_hello that shows a message box. The same steps work for any project: a dozen modules, class modules, a Workbook_Open handler.

Step 1 — Extract vbaProject.bin from a macro workbook

An .xlsm file is a zip archive, and the compiled macros live in one part of it, xl/vbaProject.bin. xlsxwriter installs a small command-line script, vba_extract.py, that copies that part into the current folder:

Bash

cd template && vba_extract.py macros_template.xlsm && ls -l vbaProject.bin

If the project is digitally signed, the script also writes vbaProjectSignature.bin. The same thing takes three lines of Python, which is handy in a build script:

Python

import zipfile

with zipfile.ZipFile("template/macros_template.xlsm") as z:
    data = z.read("xl/vbaProject.bin")
with open("vbaProject.bin", "wb") as f:
    f.write(data)
print(f"{len(data):,} bytes")

Commit vbaProject.bin next to the report code. When a macro changes, open the template in Excel, edit, save, and extract again; the binary is the build artefact and the template is its source. If you also want the VBA source in version control, where it can be reviewed and diffed, see Extract VBA Macro Code from an Excel File.

Step 2 — Attach it to a new workbook

add_vba_project() takes the path to the binary. The file name must end in .xlsm:

Python

import xlsxwriter

wb = xlsxwriter.Workbook("weekly_report.xlsm")
ws = wb.add_worksheet("Report")
ws.write_row("A1", ["Region", "Sales"])
for row, (region, sales) in enumerate([("North", 1250), ("South", 980), ("West", 1430)], start=1):
    ws.write_row(row, 0, [region, sales])
print("add_vba_project returned", wb.add_vba_project("vbaProject.bin"))
wb.close()

It returns 0 when the file exists and -1, with a warning, when the path is wrong. The warning is easy to miss in a scheduled job, so check the return value and stop the job (here it just prints; in production, raise):

Python

wb = xlsxwriter.Workbook("broken.xlsm")
wb.add_worksheet()
if wb.add_vba_project("wrong/vbaProject.bin") != 0:
    print("VBA project not found - refusing to ship a report without its macros")
wb.close()

xlsxwriter does not check the extension. Saved as .xlsx, the file still declares macro-enabled content, and Excel refuses to open it because the format and the extension do not match. Always use .xlsm when calling add_vba_project().

Step 3 — Add a button that runs a macro

A form-control button is the usual way to let users run a macro. insert_button() places one over a cell and links it to a procedure by name:

Python

wb = xlsxwriter.Workbook("report_with_button.xlsm")
ws = wb.add_worksheet("Report")
ws.write("A1", "Press the button to run the macro")
ws.insert_button("B3", {
    "macro": "say_hello",
    "caption": "Say hello",
    "width": 120,
    "height": 30,
})
wb.add_vba_project("vbaProject.bin")
wb.close()

The macro value is the name of a public Sub in a standard module. Without it, xlsxwriter links the button to Button1_Click, which almost certainly does not exist in your project. The options dictionary is not validated: a misspelled key such as "marco" is silently ignored, leaving you with the default, so read the result back as in Step 5.

Step 4 — Keep the code names consistent

VBA refers to the workbook and its sheets by code names: ThisWorkbook, Sheet1, Sheet2. Event handlers such as Workbook_Open live in the ThisWorkbook module and only fire if the workbook's code name matches the module in the project. xlsxwriter sets ThisWorkbook and Sheet1, Sheet2… by default once a VBA project is added:

Python

import re
import zipfile

wb = xlsxwriter.Workbook("two_sheets.xlsm")
wb.add_worksheet("Data")
wb.add_worksheet("Summary")
wb.add_vba_project("vbaProject.bin")
wb.close()

with zipfile.ZipFile("two_sheets.xlsm") as z:
    for part in ("xl/workbook.xml", "xl/worksheets/sheet1.xml", "xl/worksheets/sheet2.xml"):
        names = re.findall(r'codeName="(\w+)"', z.read(part).decode())
        print(f"{part:26} {names}")

Those defaults match a project saved from an English-language Excel. A template saved in a localised Excel uses translated names, for example DieseArbeitsmappe and Tabelle1 in German, and code that refers to a sheet by its code name expects the same. Set them explicitly to whatever the project uses:

Python

wb = xlsxwriter.Workbook("german_template.xlsm")
wb.set_vba_name("DieseArbeitsmappe")
ws = wb.add_worksheet("Daten")
ws.set_vba_name("Tabelle1")
wb.add_vba_project("vbaProject.bin")
wb.close()

The names to use are the module names you see in the VBA editor's project tree, or the .cls module names that oletools lists when it reads the template.

Step 5 — Check the macro is in the file

Nobody wants to find out from a user that the button does nothing. Verify the output in the same job, without Excel: the part must be present, the button must point at the right macro, and the procedure must be in the project.

Python

from oletools.olevba import VBA_Parser

def check_macro_workbook(path, macro):
    with zipfile.ZipFile(path) as z:
        names = z.namelist()
        assert "xl/vbaProject.bin" in names, "no VBA project"
        vml = "".join(z.read(n).decode() for n in names if n.endswith(".vml"))
    buttons = re.findall(r"<x:FmlaMacro>\[0\]!(\w+)", vml)
    parser = VBA_Parser(path)
    code = "\n".join(src for _, _, _, src in parser.extract_macros())
    parser.close()
    procedures = re.findall(r"^\s*(?:Public\s+)?Sub\s+(\w+)", code, re.MULTILINE | re.IGNORECASE)
    print(f"buttons -> {buttons}; procedures in project -> {procedures}")
    missing = [b for b in buttons if b not in procedures]
    assert macro in procedures, f"{macro} is not in the project"
    assert not missing, f"buttons point at missing macros: {missing}"
    return "ok"

print(check_macro_workbook("report_with_button.xlsm", "say_hello"))

The check catches the three common failures: a missing binary, a button linked to a macro that was renamed in the template, and a stale vbaProject.bin that predates a new procedure.

Step 6 — Edit the file later without losing the macros

If another step opens the report with openpyxl, for example to add a sheet, pass keep_vba=True. Without it, openpyxl drops the VBA project on save:

Python

from openpyxl import load_workbook

for keep in (True, False):
    wb = load_workbook("report_with_button.xlsm", keep_vba=keep)
    wb["Report"]["A2"] = "edited by openpyxl"
    out = f"edited_keep_vba_{keep}.xlsm"
    wb.save(out)
    with zipfile.ZipFile(out) as z:
        print(f"keep_vba={keep!s:5}  vbaProject.bin kept: {'xl/vbaProject.bin' in z.namelist()}")

Work with Macro-Enabled .xlsm Files in openpyxl covers that route in more detail. The simpler design is to do all the writing in one xlsxwriter pass and attach the project last.

When not to ship a macro

A macro in a generated report is code that runs on every recipient's machine, and many organisations block macros in files from the internet or email by default: users see a security banner, or nothing at all, and the button silently does nothing. Before adding one, ask whether the work could happen in Python before the file is written. Refreshing data, applying formatting, building a summary sheet and splitting a file per region are all easier to test in Python. Translate Common VBA Macros to Python shows the usual equivalents, and Python vs VBA for Excel Automation weighs the two. Macros earn their place when the user needs to act inside Excel after the report arrives: re-running a calculation with their own inputs, or pushing a selection somewhere.

If a macro only needs to run once, on your side, run it instead of shipping it: Run an Excel Macro from Python with win32com.

Common pitfalls

Saving as .xlsx. xlsxwriter writes the macro content type regardless of the name; Excel rejects the mismatch. Use .xlsm.
Ignoring the return value. A wrong path produces a warning and a workbook without macros; check for -1.
Button linked to the default macro. Without macro, the button calls Button1_Click. Misspelled option keys are ignored.
Localised code names. Event handlers in DieseArbeitsmappe never fire in a workbook named ThisWorkbook; set set_vba_name() to match the template.
Round-tripping through openpyxl without keep_vba. The project is dropped on save with no warning.
Editing the binary. vbaProject.bin is compiled and compressed; change macros in Excel and extract again rather than patching the file.

Conclusion

Write macros once in Excel, extract xl/vbaProject.bin with vba_extract.py or zipfile, and commit it with the report code. Attach it with add_vba_project() to a workbook named .xlsm, check the return value, link buttons with insert_button({"macro": ...}), and set code names with set_vba_name() when the template came from a non-English Excel. Verify the output with zipfile and oletools in the same job, and prefer moving logic into Python when the macro would only automate what the script could do before saving.

Frequently asked questions

Can xlsxwriter create VBA macros from source code? No. xlsxwriter embeds a compiled vbaProject.bin that Excel produced. Write the macros in Excel's VBA editor, save a .xlsm file, and extract the binary with vba_extract.py.

Why does Excel say the file format or extension is not valid? The workbook contains a VBA project but was saved with a .xlsx extension. xlsxwriter does not check the name, so save workbooks with add_vba_project() as .xlsm.

Why does my Workbook_Open macro not run? Check that macros are enabled for the file, and that the workbook's code name matches the module in the project. A template from a German Excel uses DieseArbeitsmappe; call set_vba_name() with that name.

Can I add a macro to an existing workbook with Python? xlsxwriter only writes new files. To keep macros while editing an existing .xlsm, use openpyxl with keep_vba=True. Adding a new project to an existing file means rewriting it with xlsxwriter or using Excel itself through win32com.

Up one level: Automating Excel with COM and pywin32 — driving Excel and its macros from Python.
Extract VBA Macro Code from an Excel File — reading the source back out.
Work with Macro-Enabled .xlsm Files in openpyxl — editing cells without losing the project.
Run an Excel Macro from Python with win32com — running macros instead of shipping them.
Building Excel Reports with xlsxwriter — the rest of the writer.

More on macros & vba