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.
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:
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:
- macro.xlsm!: the file that contains the VBA macro, no need to include the full path here
- Module1: the module that contains the macro
- 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.