How to Run Excel VBA Macro From Python

Sharing is caring!

Last Updated on March 26, 2022 by Jay

Sometimes we might want to run Excel VBA Macro from Python. This is useful when we want to run existing Excel macros – we can execute those VBA macros without even opening the Excel file.

Library

We’ll use the py32win library for this demonstration. Note the py32win library doesn’t have a “normal” naming convention. To install it, type the following in a command prompt:

pip install pywin32

However, to import the library in Python, the name becomes win32com.client:

import win32com.client

Excel VBA Macro

Here’s a very simple Excel Vba macro – it takes an argument, writes a greeting message in cell B2 of the “Sheet1” tab. You can create any macro and run it with Python.

Sub macro1(name As String):
    Worksheets("Sheet1").Range("B2").Value = "hello " & name & "!"
End Sub

Note in the below screenshot, this macro has the name macro1, and it’s inside Module1 of the workbook.

Run VBA Macro From Python

Now we’ll write the Python code, starting with importing win32com.client and instantiating an “xl” object to represent the Microsoft Excel application.

Then we open the Excel file that contains the VBA macro and assign it to a variable wb.

import win32com.client
xl = win32com.client.Dispatch("Excel.Application")  #instantiate excel app

wb = xl.Workbooks.Open(r'C:\Users\jay\Desktop\PythonInOffice\python_run_macro\macro.xlsm')
xl.Application.Run('macro.xlsm!Module1.macro1("Jay")')
wb.Save()
xl.Application.Quit()

To call the VBA Macro, we use the xl.Application.Run() method. The argument indicates which macro to run: xl.Application.run(‘macro.xlsm!Module1.macro1(“Jay”)’). Let’s break this down:

1. macro.xlsm!: the file that contains the VBA macro, no need to include the full path here
2. Module1: the module that contains the macro
3. macro1(“Jay”): the name of the macro, note we can pass arguments into VBA.

If your VBA macro doesn’t take any arguments, then just include the macro name without the brackets. For example: xl.Application.run(‘macro.xlsm!Module1.macro1’)

We then save the Excel file using wb.Save(). You can choose to use the VBA code to save the workbook too.

Last but not least, don’t forget to close the Excel app (in Python) by calling the xl.Application.Quit(). Leaving the Excel Application open in the background might cause problems with your program later on.

Additional Resources

Automate Word Document (.docx) With Python-docx And pywin32

Mail Merge Send Mass Email In Python

Get Outlook calendar meeting data using Python