Automating Excel With xlwings: The Basics

xlwings drives a running copy of Excel from Python. Instead of editing the file bytes on disk, it talks to the Excel application over COM (Windows) or AppleScript (macOS), so charts recalculate, formulas evaluate, and VBA macros run exactly as they would if you clicked the buttons yourself. That makes it the right tool for the last mile of reporting — populating a formatted template, refreshing a pivot, and handing back a workbook a colleague can open without surprises.

The trade-off is that xlwings needs a real Excel install on the same machine. This guide walks through the core objects — App, Book, Sheet, Range — and a complete, safe automation pattern you can adapt.

What xlwings needs to run

xlwings is a bridge to the desktop Excel application, so it requires:

• Microsoft Excel installed locally — Excel 2010+ on Windows, or Excel 2016+ on macOS. On Windows it uses pywin32; on macOS it uses AppleScript.
• An interactive desktop session. Excel launches as a GUI process even when hidden, so xlwings does not work on headless Linux, plain Docker containers, or CI runners like GitHub Actions.

If you need to read or write .xlsx files on a server or in CI with no Excel present, use the file-based libraries instead: see Reading Excel Files with Pandas for ingestion and Using openpyxl for Excel File Manipulation for direct file editing. A common split is to do data work headless and reserve xlwings for final formatting and macro-driven delivery on a workstation.

Install xlwings

pip install xlwings

That single package pulls in the platform dependencies it needs (pywin32 on Windows). You do not need to install Excel through pip — xlwings connects to the copy already on your machine.

Open a workbook

The quickest way to get a live workbook is xw.Book. With no argument it creates a new blank workbook in a visible Excel window; pass a path to open an existing file:

import xlwings as xw

# Create a new blank workbook (opens a visible Excel window)
book = xw.Book()

# Or open an existing file
book = xw.Book(r"C:\reports\sales.xlsx")

xw.Book is convenient for interactive work because it reuses an Excel instance if one is already open. For scheduled jobs you usually want full control over the Excel process — that means starting your own xw.App, which the production pattern below uses.

Target a sheet

A Book holds a collection of sheets. Select one by name (or index) to get a Sheet object:

sheet = book.sheets["Raw_Data"]   # by name
sheet = book.sheets[0]            # by position (first sheet)

# Add a new sheet and make it the active one
new_sheet = book.sheets.add("Summary")

Read and write cell values

A Range is the workhorse object. Index a sheet with a cell address (or call sheet.range(...)), then use the .value property to read or assign:

sheet = book.sheets["Raw_Data"]

# Write a single value
sheet["A1"].value = "Order ID"

# Read it back — .value coerces Excel types to Python types
header = sheet["A1"].value          # -> "Order ID"

# Write a 2D block in one COM call (rows of columns)
sheet["A1"].value = [
    ["Order ID", "SKU", "Quantity"],
    [1001, "A-100", 3],
    [1002, "B-200", 1],
]

Assigning a list of lists to the top-left cell writes the whole block in a single round trip to Excel. That bulk transfer is far faster than looping cell by cell, so prefer it whenever you have tabular data.

Write a list down a column

A plain 1D Python list is written horizontally by default — it fills a row, not a column. To lay a list out vertically, use .options(transpose=True):

ids = [1001, 1002, 1003, 1004]

# Horizontal (default): fills A1, B1, C1, D1
sheet["A1"].value = ids

# Vertical: fills A1, A2, A3, A4
sheet["A1"].options(transpose=True).value = ids

.options() is also how you control the shape of what you read back — for example sheet["A1:A4"].options(ndim=1).value returns a flat list rather than a list of one-item lists.

Run a VBA macro

xlwings can invoke a VBA Sub by name and run it synchronously. Get a callable with app.macro() (application-scoped) or book.macro() (workbook-scoped), then call it. Both forms work in current xlwings — app.macro() was simply added later, in 0.24.0, to allow application-level resolution:

# Application-scoped: qualify with the workbook name
run = book.app.macro(f"'{book.name}'!RefreshPivotTables")
run()

# Workbook-scoped form — equivalent and still supported
book.macro("RefreshPivotTables")()

Macros only exist in macro-enabled files (.xlsm, .xlsb, or .xlam); a plain .xlsx cannot store VBA. Arguments are passed positionally, e.g. run("North", 2024). For a fuller treatment — parameter passing, personal-macro workbooks, and the errors you will hit — see the xlwings run macro from Python example.

A complete, safe automation pattern

For anything unattended, manage the Excel process yourself with xw.App and guarantee cleanup with try/finally. If an exception escapes before app.quit(), you leave an orphaned EXCEL.EXE holding a file lock. The pattern below opens a template, writes data in bulk, runs a macro, saves a dated copy, and always shuts Excel down:

import xlwings as xw
from pathlib import Path
from datetime import datetime


def generate_report(template_path: str, output_path: str, rows: list) -> None:
    """Populate an Excel template, refresh it via VBA, and save a copy."""
    # add_book=False: don't create an empty workbook alongside the template
    app = xw.App(visible=False, add_book=False)
    book = None
    try:
        book = app.books.open(str(Path(template_path).resolve()))
        sheet = book.sheets["Report"]

        # Stamp the run time
        sheet["B2"].value = f"Generated: {datetime.now():%Y-%m-%d %H:%M}"

        # Bulk-write the data block starting at A5
        sheet["A5"].value = rows

        # Tidy the layout
        sheet["A5"].expand().columns.autofit()

        # Refresh pivots / charts via a VBA macro in the template
        book.macro("RefreshPivotTables")()

        book.save(str(Path(output_path).resolve()))
    finally:
        if book is not None:
            book.close()
        app.quit()


if __name__ == "__main__":
    data = [
        ["2024-01-05", "North", "Widget A", 12500],
        ["2024-01-12", "South", "Widget B", 18300],
        ["2024-01-18", "East", "Widget C", 9400],
    ]
    output = f"sales_report_{datetime.now():%Y%m%d}.xlsx"
    generate_report("monthly_template.xlsm", output, data)

A few decisions in that code are worth calling out:

• visible=False runs Excel hidden, which is what you want for a scheduled job. Flip it to True while developing so you can watch the workbook fill in and catch VBA dialogs.
• add_book=False stops xlwings from spawning a throwaway blank workbook every time you start an App.
• Absolute paths via pathlib. A scheduler (cron, Task Scheduler) runs with a different working directory than your shell, so relative paths break. Path(...).resolve() removes the ambiguity.
• Cleanup in finally. book.close() then app.quit() runs on both success and failure, so no zombie Excel process survives.

Common pitfalls

• Orphaned Excel processes. Almost always a missing try/finally. If one lingers, it keeps the output file locked and the next run fails. Make app.quit() unconditional.
• Workbook not found / file-not-found. Usually a relative path under a scheduler. Resolve to an absolute path, as above.
• A 1D list landed in a row, not a column. Add .options(transpose=True) before .value.
• com_error: CoInitialize has not been called. Happens when you drive xlwings from a background thread or async framework. Call pythoncom.CoInitialize() on that thread first, or run the automation on a dedicated synchronous thread.
• Macro silently does nothing. Confirm the file is .xlsm/.xlsb/.xlam and that the macro name (and workbook qualifier) match exactly.

Conclusion

xlwings is the right tool for the last mile on a Windows or macOS workstation: populate a formatted template, run a VBA refresh, and hand back a polished workbook. It is the wrong tool for headless servers, CI runners, and anything that needs to run without Excel present — use pandas and openpyxl there. The pattern that scales best is to do all data work headless and reserve xlwings only for the final, presentation-layer step on a machine where Excel is installed.

Where to go next

xlwings is the presentation layer; keep data extraction and transformation decoupled from formatting. Prepare and validate your data with pandas, then hand a clean table to xlwings for the final, formatted workbook — see Writing DataFrames to Excel with Pandas for staging the table, and the xlwings run macro from Python example to drive existing VBA. For the wider picture, start from the Getting Started with Python Excel Automation overview.