Can Python Replace VBA? Master Excel Automation with xlwings

VBA and Python: When King of Glory Meets the King of Scripts

VBA is a subset of Visual Basic, widely used in many software for scripting, such as Excel, Word, PowerPoint, GIS, CAD, statistical and graphic tools.

In recent years Python has risen to the top three languages, offering simplicity, open‑source nature, and extensive libraries for system operations, networking, scientific computing, data analysis, and visualization.

Python is often called a "glue language" and is increasingly used as a scripting language alongside VBA in applications like ArcGIS and SPSS, and can replace VBA in Excel through third‑party packages.

Python packages related to Excel

Common third‑party packages include xlrd, xlwt, OpenPyXL, win32com, and xlwings. The latter two provide full access to Excel's COM object model, making Python capable of doing everything VBA can.

Why xlwings can fully replace VBA

win32com exposes the same object model as VBA; xlwings builds on win32com, offering a higher‑level API. Thus any VBA script can be rewritten with xlwings or win32com.

xlwings quick demo

Creating ten worksheets:

import xlwings as xw
app = xw.App()
bk = app.books(1)
for i in range(10):
    bk.api.Worksheets.Add(After=bk.api.Worksheets(bk.api.Worksheets.Count))

Adding a chart from worksheet data:

import xlwings as xw
app = xw.App()
wb = app.books.active
sht = wb.sheets.active
cht = sht.charts.add(50, 200)
cht.set_source_data(sht.range("A1").expand())
cht.chart_type = "column_clustered"
cht.api[1].HasTitle = True

Key features of xlwings

Operate workbooks, worksheets, cells, and ranges.

Create and edit Excel graphics and charts.

Create and edit pivot tables.

Process data using Excel functions.

SQL handling within Excel.

Convert between Python types (NumPy, pandas) and Excel.

Mixed Python‑VBA programming.

Is xlwings easy to learn?

xlwings offers two usage styles: a VBA‑like API and a higher‑level syntax. Users familiar with VBA can quickly adopt the API, while the new syntax provides more concise code.

Example of selecting a row with VBA‑style API:

# VBA style
sht.api.Rows(1).Select()
# xlwings new syntax
sht["1:1"].select()

Reading a column into a Python list:

lst = sht.range("A1:A5").value
# returns [1.0, 2.0, 3.0, 4.0, 5.0]

Recommended book

“Replace VBA! Use Python to Easily Implement Excel Programming” covers xlwings basics, the Excel object model, files, graphics, charts, regular expressions, pandas analysis, and mixed programming.