Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Extract VBA Macro Code from an Excel File

Read the VBA inside .xlsm, .xlsb and .xls files without opening Excel: detect whether a workbook has macros at all, extract every module with oletools, save them as .bas and .cls files that can be diffed and reviewed in git, inventory the macros across a folder, and flag suspicious code before anyone enables it.
Macros & VBAFile formats & conversion

Macro-enabled workbooks accumulate code that nobody has read in years: the month-end file with a button that "does the import", the template whose Workbook_Open runs something before anyone sees the sheet. Migrating those macros to Python, reviewing them for security, or simply putting them under version control all start with getting the source code out. Excel stores VBA inside the workbook in a compressed binary container, vbaProject.bin, and the oletools package can decompress and read it on any operating system, without Excel and without running a single line of the macros. This page is part of Automating Excel with COM and pywin32.

Prerequisites

Bash
pip install oletools openpyxl

The inbox/ folder holds two macro-enabled workbooks and one ordinary .xlsx. The macro workbooks contain a small VBA project with a standard module, Module1, holding a say_hello procedure, and the class modules Excel creates for the workbook and each sheet. oletools reads .xlsm, .xlsb, legacy .xls and even Word files the same way.

From a workbook to reviewable source files The workbook is opened as a zip or OLE container, the vbaProject.bin part is found, oletools decompresses each module's source, empty document modules are skipped, and each remaining module is written to a .bas or .cls file. Workbook .xlsm / .xlsb / .xls vbaProject.bin compressed OLE part Decompress oletools VBA_Parser Skip empty attributes only .bas / .cls diffable text

Step 1 — Does the workbook have macros?

For .xlsm and .xlsb files the VBA project is a part of the zip archive; its presence is a quick first test. VBA_Parser gives the definitive answer for every format, including old .xls files that are not zip archives:

Python
import zipfile
from pathlib import Path
from oletools.olevba import VBA_Parser

for path in sorted(Path("inbox").iterdir()):
    in_zip = zipfile.is_zipfile(path) and "xl/vbaProject.bin" in zipfile.ZipFile(path).namelist()
    parser = VBA_Parser(str(path))
    print(f"{path.name:18} container={parser.type:9} vbaProject.bin={in_zip!s:5} macros={parser.detect_vba_macros()}")
    parser.close()

The file extension is only a hint — an .xlsx renamed to .xlsm has no macros, and a macro file renamed to .xlsx still does, though Excel refuses to open it with the wrong extension. Always check the content. Neither check runs any code: they read the file’s structure, which makes them safe to run on files from unknown senders.

Step 2 — Extract the modules

extract_macros() yields one tuple per module: the container file, the stream path, the module's file name and its source code. Many modules are document modules that Excel creates for the workbook and each sheet; when nobody wrote code in them they contain only Attribute lines. Keep the ones with real code:

Python
def modules(path):
    parser = VBA_Parser(str(path))
    try:
        for _, stream, filename, code in parser.extract_macros():
            body = [l for l in code.splitlines() if l.strip() and not l.startswith("Attribute ")]
            yield filename, code, len(body)
    finally:
        parser.close()

for filename, code, lines in modules("inbox/month_end.xlsm"):
    print(f"{filename:18} {lines} line(s) of code")
print()
print(next(code for filename, code, lines in modules("inbox/month_end.xlsm") if filename == "Module1.bas"))

Module1.bas holds the only real code — the say_hello procedure — while the workbook and sheet modules are empty apart from their attributes. The Attribute lines are metadata Excel writes into every module: its name, and for document modules the link to the object they belong to. They are needed to re-import a module into Excel, but not for reading it.

The modules oletools finds in the project A table of the VBA modules in the workbook with their type and the number of lines of real code. The standard module Module1 has three lines; the workbook and sheet document modules have none. A B C 1 Module Kind Lines of code 2 Module1.bas standard module 3 3 ThisWorkbook.cls workbook document module 0 4 Sheet1.cls sheet document module 0 5 Sheet2.cls sheet document module 0 6 ThisWorkbook1.cls workbook document module 0

Step 3 — Save them as files for version control and review

Write each module with real code to its own file, named after the workbook, so the macros can be committed, diffed and reviewed like any other code:

Python
def export_modules(path, out_root="vba_src"):
    out = Path(out_root) / Path(path).stem
    out.mkdir(parents=True, exist_ok=True)
    written = []
    for filename, code, lines in modules(path):
        if lines:
            (out / filename).write_text(code.replace("\r\n", "\n"), encoding="utf-8")
            written.append(str(out / filename))
    return written

print(export_modules("inbox/month_end.xlsm"))

Line endings are normalised to \n — VBA stores them as \r\n — so git does not show every line as changed between Windows and Linux checkouts. Running the export as a pre-commit hook, or in CI whenever an .xlsm changes, keeps a readable history of the macros next to the binary workbook — the same idea as Diff Excel Files in Git with a textconv Driver for cell contents.

List the procedures in each module

For migration planning, the procedure names and their signatures are more useful than the full text: they are the "things the workbook does". A regular expression over the extracted code lists them:

Python
import re

PROC = re.compile(r"^\s*(?:(Public|Private|Friend)\s+)?(?:Static\s+)?(Sub|Function|Property\s+(?:Get|Let|Set))\s+(\w+)\s*\(([^)]*)\)",
                  re.IGNORECASE | re.MULTILINE)

def procedures(path):
    out = []
    for filename, code, lines in modules(path):
        for scope, kind, name, args in PROC.findall(code):
            out.append({"module": filename, "kind": kind.title(), "name": name,
                        "scope": scope or "Public", "arguments": args.strip() or "—"})
    return out

print(procedures("inbox/month_end.xlsm"))

say_hello is the workbook's one procedure: a public Sub with no arguments, which in VBA also means it appears in Excel's macro list and can be attached to a button. Event handlers show up the same way — Workbook_Open, Worksheet_Change — and are worth marking, because they run without anyone choosing to run them. Across a folder of workbooks, this list becomes the backlog of a migration: each procedure is a function to rewrite, test and retire.

Step 4 — Flag suspicious code before anyone enables it

Workbooks from outside the organisation deserve a look before anyone clicks Enable Content. analyze_macros() scans the code for keywords that matter: procedures that run automatically (AutoOpen, Workbook_Open), calls that can reach outside Excel (Shell, CreateObject, file and network access) and obfuscation tricks:

Python
def review(path):
    parser = VBA_Parser(str(path))
    try:
        return [(kind, keyword) for kind, keyword, _ in parser.analyze_macros()]
    finally:
        parser.close()

print(review("inbox/month_end.xlsm"))

The only finding here is Hex Strings — raised by the GUIDs in the modules' Attribute lines, which look like hexadecimal data. It is a false positive, and a reminder of how to read the output: AutoExec and Suspicious findings such as Shell, CreateObject or URLDownloadToFile in code that also runs automatically are the combination to worry about; a hex string alone is not. olevba on the command line gives the same analysis as a table, and mraptor from the same package gives a simple verdict for automated screening.

Reading the analysis A decision diagram for olevba findings. No macros means nothing to review. Macros with no AutoExec and no suspicious calls are low risk. AutoExec alone deserves a read. AutoExec together with Shell, CreateObject or download calls means do not enable the macros until reviewed. What did analyze_macros report? nothing Low risk read if migrating AutoExec only Read the open handler runs on open AutoExec + Shell/Download Do not enable review first hex strings only Usually harmless GUIDs, constants

Step 5 — Inventory a folder of workbooks

Before a migration or an audit, it helps to know which files have macros, how much code, and whether any run on open. One row per workbook:

Python
import pandas as pd

def inventory(folder):
    rows = []
    for path in sorted(Path(folder).glob("*.xls*")):
        parser = VBA_Parser(str(path))
        has = parser.detect_vba_macros()
        parser.close()
        mods = [(f, n) for f, _, n in modules(path)] if has else []
        findings = review(path) if has else []
        rows.append({"file": path.name, "macros": has,
                     "modules with code": sum(1 for _, n in mods if n),
                     "lines": sum(n for _, n in mods),
                     "runs on open": any(k == "AutoExec" for k, _ in findings)})
    return pd.DataFrame(rows)

inv = inventory("inbox")
print(inv.to_string(index=False))
inv.to_excel("macro_inventory.xlsx", index=False)

The inventory is the starting point for Translate Common VBA Macros to Python: the files with the most lines and the ones that run on open are where a migration should start.

Common pitfalls

  • Trusting the extension. Check for a VBA project in the content; extensions lie.
  • Opening untrusted files in Excel to read their macros. oletools reads the code without running it; Excel may run it.
  • Treating every finding as malicious. Hex strings and GUIDs are normal; look for automatic execution combined with outside access.
  • Losing the attributes. Keep them in exported files if the modules will be imported back into Excel.
  • Password-protected VBA projects. The protection only hides the code in Excel's editor; oletools can usually still extract it, which is worth knowing both for migrations and for anyone relying on it as security.

Conclusion

Use oletools' VBA_Parser to detect and extract VBA from .xlsm, .xlsb and .xls files without Excel: detect_vba_macros() to find macro files regardless of extension, extract_macros() for each module's source, skipping document modules that hold only attributes, and analyze_macros() to flag automatic execution and outside access before anyone enables content. Export real modules as .bas and .cls files for version control, and inventory whole folders to plan a migration.

Frequently asked questions

How can I read VBA code from an Excel file without opening Excel? Install oletools and use olevba on the command line or VBA_Parser in Python; extract_macros() returns each module's name and source code. It works on Windows, macOS and Linux and does not run the macros.

Can openpyxl read VBA macros? No. openpyxl can keep the VBA project when saving with keep_vba=True, but it does not decompress or expose the code. Use oletools to read it.

Why are some extracted modules empty? Excel creates a document module for the workbook and for each sheet. If no code was written in them, they contain only Attribute lines. Skip them when exporting for review.

How do I check a macro workbook for malicious code? Run olevba or VBA_Parser.analyze_macros() and look for AutoExec keywords such as Workbook_Open combined with Suspicious calls such as Shell, CreateObject or download functions. Hex strings on their own are common and usually harmless.

More on macros & vba

More on macros & vba →