How Running a Python Script From Excel With xlwings Works

Sometimes the job needs Python and also needs a live Excel: a button the user clicks, a range the script reads from the open sheet, a Power Query that must be refreshed, a chart updated in place. Libraries like openpyxl cannot do that, because they edit files and never talk to the application. xlwings can. It drives Excel from Python, and it lets Excel call Python. This post shows the setup, the basic read and write calls, a button that runs a script, and what limits the approach. It is a spoke of automating Excel work without clicking.

What xlwings is

xlwings is a Python package that talks to Excel through its automation interface (COM on Windows, AppleScript on macOS). It works in both directions. From Python, you can open workbooks, read and write ranges and call Excel features. From Excel, a VBA macro or an add-in can call a Python function. The core library is open source under the BSD license, and some features, such as report templates and embedding code in the workbook, are part of a paid tier (xlwings documentation describes the split).

It is the bridge approach described in VBA versus Python for Excel automation: you get Python's libraries and Excel's live features together, at the price of requiring desktop Excel.

Install and create a project

Install into the Python that will run the scripts:

python -m pip install xlwings
xlwings addin install
xlwings quickstart report_tool

xlwings addin install puts the Excel add-in on the ribbon. xlwings quickstart report_tool creates a folder with two files: report_tool.xlsm, a macro enabled workbook, and report_tool.py, a Python file of the same name. xlwings pairs them by name, so the workbook calls the functions in the file next to it.

Open the .xlsm and allow macros for this file. The workbook includes a button on its sheet wired to a macro that runs main from the Python file.

The Python side

The generated report_tool.py looks like this:

import xlwings as xw

def main():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

xw.Book.caller() is the key line: it returns the workbook that called the function, so the script works with whichever copy of the file the user has open, in any folder. It avoids hard-coded paths. Everything else is the object model: wb.sheets[0], sheet["A1"].value.

Replace main with real work. A script that reads an input range, computes and writes a result:

import xlwings as xw
import pandas as pd

def summarize():
    wb = xw.Book.caller()
    sht = wb.sheets["Data"]

    df = sht.range("A1").options(pd.DataFrame, expand="table", index=False).value
    out = df.groupby("region", as_index=False)["amount"].sum()

    res = wb.sheets["Summary"]
    res.range("A1").value = out
    res.range("A1").expand().columns.autofit()

options(pd.DataFrame, expand="table") reads the whole contiguous block starting at A1 into a DataFrame in one call. Assigning a DataFrame to .value writes it back in one call. Reading and writing ranges as blocks is the single most important habit, since each cell-by-cell call crosses the process boundary and a loop over 10,000 cells can take minutes where the block version takes a second.

The Excel side

The button calls a VBA stub that starts the Python function. The quickstart workbook contains:

Sub SampleCall()
    mymodule = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
    RunPython ("import " & mymodule & ";" & mymodule & ".main()")
End Sub

RunPython imports the module named like the workbook and calls its function. To call summarize instead, change main() to summarize() or add another macro and another button. Newer versions of xlwings offer a ribbon-based "Run main" and, in the add-in's Interpreter settings, a place to set which Python to use. If the add-in cannot find Python, set the interpreter path there; it is the most frequent setup problem.

The Python code runs in a separate process on your machine. Print output goes to the console, or to a log file if you configure one. Errors raised in Python are shown in a dialog in Excel. That means a typo in the script appears as a message box, which is useful for development and poor for users, so catch expected errors and write a plain message into a cell.

User-defined functions

On Windows, xlwings can expose Python functions as worksheet formulas. A function decorated with @xw.func is callable from a cell:

import xlwings as xw

@xw.func
def double(x):
    return x * 2

After importing the UDFs from the add-in, =double(A1) works in a cell. This is handy for calculations that are easy in Python and ugly in formulas. It is also a trap for large models, because each call crosses the process boundary, and a column of 100,000 UDF cells is slow. UDFs are Windows only. Microsoft's own Python in Excel is a different feature that runs in a hosted environment, with its own libraries and limits; check Microsoft's documentation to see whether it suits a task before building on xlwings.

Doing things only Excel can do

The reason to pick xlwings over openpyxl is access to the application. Examples of calls that go through to Excel:

wb = xw.Book.caller()
wb.api.RefreshAll()                      # refresh queries and pivots
wb.sheets["Dash"].api.ChartObjects(1).Chart.Refresh()
wb.app.calculate()                       # recalc everything

.api gives the underlying Excel object, so anything you can do in VBA you can do from Python with the same names. This covers refreshing a Power Query, updating a pivot, saving as PDF with wb.to_pdf() in recent versions, or setting calculation mode. When you use a feature that runs asynchronously, such as a query refresh, wait for it to finish before saving, as in the Power Query scheduling post (refreshing Power Query data on a schedule).

Running without a button

xlwings can also run a script from the command line, to open a workbook, do the work and save it, with no user:

import xlwings as xw

with xw.App(visible=False) as app:
    wb = app.books.open(r"C:\reports\sales.xlsx")
    wb.api.RefreshAll()
    app.api.CalculateUntilAsyncQueriesDone()
    wb.save()
    wb.close()

This starts a hidden Excel instance, refreshes, saves and closes. It is useful for a Windows machine under Task Scheduler. The same caution applies as with any unattended Office automation: Microsoft does not support running desktop Office as a service, modal dialogs will hang the script, and the machine must have a user session available. For a job that matters, prefer a file-based approach that needs no Excel (automating weekly Excel reports with Python and cron).

Troubleshooting

• The button does nothing or shows a module error. The Python file must have the same name as the workbook and sit beside it, and the interpreter in the add-in settings must be the one where xlwings is installed.

• ImportError in Excel but not in the terminal. Excel launches a different Python from the one in your shell. Print sys.executable from inside a macro run to see which.

• "Programmatic access" or permission prompts on macOS. The first run asks for permission to control Excel.

• A script hangs. A dialog is waiting in the Excel window, often a save prompt or a protected view bar.

• Slow writes. Replace per-cell assignment with block assignment.

• The workbook is blocked. A downloaded .xlsm may be blocked by Office until you unblock it in file properties.

Checks

After the macro runs, verify the result outside Excel. Read the output sheet's row count and a total in the script and compare with the input. Write a small log (time, rows in, rows out) to a text file, so you can tell whether a button click did anything. If other people will use the workbook, test it on a clean machine with only the add-in and the interpreter installed, since a tool that works on the developer's machine is the norm and the real test is the second machine.

When xlwings is more than the job needs

The job reads and writes files. openpyxl or pandas does it without Excel, and the script runs anywhere (reading and writing Excel files with openpyxl).

It runs on a server. There is no Excel there.

The users are on Excel for the web or locked-down machines. Macro-enabled files and add-ins may be blocked. Consider Office Scripts or a small web tool (Office Scripts for Excel on the web).

The need is a formula. If a few lines of Python replace what a lookup would do, the formula is easier for the next person.

See the xlwings documentation for the quickstart and Microsoft's page on running macros. To convert a small result to JSON, use the CSV to JSON converter.