Running Excel VBA from Python

In wild cases, it could happen that you would like to Run Excel VBA from Python.

I found myself in a situation where there was a nice Excel report template file enhanced with some VBA.

Let’s see How we can call Excel VBA procedures in Python.

Python Code to Call VBA Procedure


import os
import win32com.client as win32

def run_excel_macro (file_path, separator_char):
    """
    Execute an Excel macro
    :param file_path: path to the Excel file holding the macro
    :param separator_char: the character used by the operating system to separate pathname components
    :return: None
    """
    xl = win32.Dispatch('Excel.Application')
    xl.Application.visible = False #change to True if you are desired to make Excel visible

    try:
        wb = xl.Workbooks.Open(os.path.abspath(file_path))
        xl.Application.run(file_path.split(sep=separator_char)[-1] + "!main.simpleMain")
        wb.Save()
        wb.Close()

    except Exception as ex:
        template = "An exception of type {0} occurred. Arguments:\n{1!r}"
        message = template.format(type(ex).__name__, ex.args)
        print(message)

    xl.Application.Quit()
    del xl

separator_char = os.sep
run_excel_macro(input('Please enter Excel macro file path: '), separator_char)

Need to Know

When you are opening the workbook you have to use the absolute path, relative path will not be enough, even if you are storing your Excel files somewhere in your Python project folder
The next step is executing the VBA script (xl.Application.run) at this point you only need the actual Excel file name and you have to refer to your macro as:

your_Excel_file_name!VBA_module_name.VBA_procedure_name

For example:

simpleMacroForPython.xlsm!main.simpleMain

Created a very simple example for presentation purposes only (simpleMacroForPython.xlsm) download and play around with it:

Enter file path and press enter: