Excel Macro to Python Script

Why convert a macro to a script at all

An Excel macro lives inside a workbook and only runs when someone opens the file in Excel. A Python script lives anywhere — your laptop, a Linux server, a Docker container, a GitHub Actions job — and runs without Excel installed. Three reasons to convert:

• Headless execution. Schedule the report instead of asking someone to open the workbook every Monday at 8am.
• Source control. A .py file diffs cleanly in git. A binary .xlsm does not.
• Reusable logic. The same Python function can power the report and a web API and a notebook. VBA can't leave Excel.

Step 1: Extract the macro from Excel

You need the VBA source as text before you can convert it. Two options:

Option A — Manual export from the VBA editor

1. Open the .xlsm workbook in Excel.
2. Press Alt+F11 to launch the VBA editor.
3. In the Project Explorer (left panel), right-click the module containing your macro.
4. Choose Export File... and save it as Module1.bas.
5. Open the .bas file in any text editor — that's your VBA source.

Option B — Programmatic extraction with olevba

For batch jobs (or when you don't have Excel installed), the oletools library extracts VBA directly from .xlsm files at the file format level.

pip install oletools

# Extract all VBA from a workbook
olevba report.xlsm > report.bas

# Or in Python:
from oletools.olevba import VBA_Parser
vba = VBA_Parser("report.xlsm")
for (filename, stream_path, vba_filename, vba_code) in vba.extract_macros():
    print(vba_code)

Step 2: Convert the VBA to Python

Paste the .bas contents into the VBAtoPython converter (or upload the file directly). The converter is deterministic — same input always produces the same output — and it flags constructs that need manual review (GoTo, On Error,ReDim Preserve patterns).

For a typical report-generation macro, here's the kind of mapping you'll see:

Sub GenerateReport()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Data")
    Dim total As Double
    total = 0

    For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(i, 2).Value > 0 Then
            total = total + ws.Cells(i, 2).Value
        End If
    Next i

    ws.Range("D1").Value = total
    ThisWorkbook.Save
End Sub

from openpyxl import load_workbook

def generate_report(path: str) -> None:
    wb = load_workbook(path)
    ws = wb["Data"]
    total = 0.0

    for i in range(2, ws.max_row + 1):
        val = ws.cell(row=i, column=2).value
        if val is not None and val > 0:
            total += val

    ws["D1"] = total
    wb.save(path)

if __name__ == "__main__":
    generate_report("report.xlsx")

Notice the four key transformations: ThisWorkbook → explicit load_workbook(path); Cells(Rows.Count, 1).End(xlUp).Row → ws.max_row; the Sub wrapper becomes a named function; and a __main__ guard makes the file runnable as a script.

Step 3: Wire up openpyxl (or pandas)

The converter outputs openpyxl calls by default because it's the closest 1-to-1 with VBA's cell-and-range mental model. Install it with:

pip install openpyxl

For tabular data work (group-by, joins, pivots) you'll often want pandas instead. The converter doesn't infer this automatically — it's a manual decision. Rule of thumb:

• Use openpyxl when the macro touches specific cells, formats, or formulas — the kind of work where row/column position matters.
• Use pandas when the macro loops over a table to compute aggregates, filters, or joins — pandas is dramatically faster for those.
• Mix both when you need pandas for the math and openpyxl for the formatting.

Step 4: Run the script from the command line

Save the converted code as generate_report.py in the same folder as report.xlsx, then run:

python generate_report.py

That's the whole loop. To accept the workbook path from the command line — handy for batch jobs and CI — wrap it with argparse:

import argparse
from openpyxl import load_workbook

def generate_report(path: str) -> None:
    # ... (same body as above)
    pass

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("workbook", help="Path to the .xlsx file")
    args = parser.parse_args()
    generate_report(args.workbook)

# Run with:
#   python generate_report.py report.xlsx
#   python generate_report.py /data/q2.xlsx

Schedule the script (the payoff)

The point of converting a macro to a script is that it can run unattended. Two common patterns:

Windows Task Scheduler

# Create a scheduled task that runs every weekday at 8am
schtasks /create /tn "Daily Report" ^
    /tr "python C:\scripts\generate_report.py C:\data\report.xlsx" ^
    /sc weekly /d MON,TUE,WED,THU,FRI /st 08:00

Linux cron

# Edit crontab
crontab -e

# Add: every weekday at 8am
0 8 * * 1-5 /usr/bin/python3 /scripts/generate_report.py /data/report.xlsx

That schedule wasn't possible with the original macro — it required someone to open Excel. Now it isn't.

Related guides

• How to Convert VBA to Python — Full Migration Guide — The 5-step process for larger projects.
• VBA For Loop Python Equivalent — Loop conversion patterns covered in detail.
• VBA to Python Cheat Sheet — One-page side-by-side reference.
• Excel VBA to Python with openpyxl — Range, Cells, and worksheet-specific patterns.