Red Oak Strategic - How To: Add & Execute Python Excel Macro

Frequently, we encounter projects that require the combined use of Python, Microsoft Excel and some external databases that can only be accessed via Excel, or use cases that require the end product to be output to that format.  Excel is still used as a key program for the vast majority of businesses and we are often challenged to create engineering and pipeline tools to combine it with the dynamic functions in Python.  We built a method to allow Python Excel macro functions to be added and executed, all from Python.

This code will review the following steps – this example was written in a Windows environment but with minor edits could be replicated in a Mac environment:

1. Generating a standard Excel (.xlsx) file from Python, using Pandas dataframes and the ExcelWriter functions
2. Creating a macro-enabled Excel file (.xlsm) from Python
3. Extracting the macros we need from a macro-enabled Excel file
4. Attaching those macros to our generated file in Python
5. Running the macros from Python
6. Saving our file

This simple code to build the Python Excel macro functions will require the following libraries:

1. pandas, both for creating our DataFrame but using the ExcelWriter module
2. pymysql, used in our example to pull data from a MySQL database: PyMySQL
3. win32com, used to refresh our macros:  PyWin32 – Windows
4. Misc. libraries like time and os

Create Data Frame

First, let’s build a data frame that can be written to our Excel file.  Of course, a user may read data from a .csv or generate a file using Python, but for this example we will read the iris dataset from a MySQL database.

Best practices in Python are to setup a secrets.json file where we can store our MySQL credentials – we will call ours excel_mysql_credentials.json.

{
"host": "localhost:3306",
"user": "username",
"password": "password",
"database": "test"
}

Now, we can import our other libraries, setup variables and read in our SQL query to Pandas using the pd.read_sql command.  We will use the standard Python iris dataset, which for this example is loaded to the testschema MySQL schema.

#IMPORT LIBARIES# 
import pandas as pd 
import pymysql 
import win32com.client 
import time as t
import os

#IMPORT CONNECTION TO MySQL#
import json
jstr = open('excel_mysql_credentials.json')
data = json.load(jstr)

#SAVE SQL CREDENTIALS TO VARIABLES#
host = data['host']
user = data['user']
password = data['password']
database = data['database']

#SET TIME VARIABLES# 
runtime = t.strftime("%Y%m%d_%H%M%S") 
date_today = t.strftime("%Y-%m-%d")
date_today_filename = t.strftime("%Y%m%d") 

print("Starting our macro refresh - started at...."+ runtime) 

#CREATE PyMYSQL CONNECTION AND CURSOR#
conn = pymysql.connect(host, user, password, database, connect_timeout=5) 
cursor = conn.cursor() 

#CREATE DATEFRAME#
pandas_sql = """SELECT * FROM testschema.table_iris""" 
df = pd.read_sql(pandas_sql, conn)

Now we have a Pandas DataFrame called df  that we can edit as needed – for our example we will simply write in a new column with today’s date.

df['timestamp'] = date_today

You can do any other edits or data munging needed using Pandas or other tools here – we will keep it simple and write out this basic DataFrame.

Extracting Macro from Existing .xlsm File

The first step before we write out our Python Excel macro file from Pandas is extracting the macro that we’ll want to attach from an existing macro-enabled Excel file.  Our tutorial assumes you are familiar with the VBA editor in Excel, compiling macros and saving them to a .xlsm (macro-enabled Excel file).  You will want to do this beforehand so that the macros are saved to an existing .xlsm file (called Macro.xlsm  in our example).

The steps for extracing a macro can be accessed here:  XLSXWriter – Working With Macros

The other method, which is an easy workaround, is to rename your .xlsm file to .zip and pull the file from the zipped file manually.

1. Rename your Macro.xlsm (replace your filename here) to Macro.zip
2. Confirm in Windows Explorer that the file extension change is OK
3. Open Macro.zip file in Windows Explorer
4. Navigate to the xl folder
5. Within that folder, there will be a file called vbaProject.bin – copy this file to your working directory.

We will reference this file later in the tutorial.  If you ever need to edit your macro, rename the .zip file back to .xlsm, edit the VBA and then repeat steps 1-4.

Save Pandas DataFrame to Excel (.xlsx) and Attach Macro

Back to Python:  saving our DataFrame to Excel is fairly straightforward.

#SAVE OUR DATAFRAME TO .XLSX#
filename = 'iris_' + date_today_filename + '.xlsx'
writer = pd.ExcelWriter(filename, engine='xlsxwriter')
df.to_excel(writer, sheet_name='iris', index=False)

Now, we’re going to attach the extracted macro to a new macro-enabled .xlsm file that we can call from Python.  We use the add_vba_project method to attach our .bin file to the .xlsm file.

#ATTACH MACRO AND CREATE .XLSM FILE# 
filename_macro = 'iris_' + date_today_filename + '.xlsm' 
workbook = writer.book
workbook.filename = filename_macro
workbook.add_vba_project('vbaProject.bin')
writer.save()

Use Win32 Library to Run Macros from Python

The final step in our Python Excel macro process is to reference our file, use Win32 to run Excel from Python and execute our macros.  It’s important to note that in our last macro (Macro3 in this example), we have VBA code to save our Excel file.  We have also had cases to write a VBA macro to save that Excel to .csv for easier import to a database – it’s up to you!

#REFERENCE FILE PATH AND RUN MACROS#
if os.path.exists(filename_macro):
    xl = win32com.client.Dispatch('Excel.Application')
    xl.Workbooks.Open(Filename = filename_macro, ReadOnly=1)
    xl.Application.Run("Macro1")
    xl.Application.Run("Macro2")
    xl.Application.Run("Macro3")
    xl.Application.Quit()
    del xl

#PRINT FINAL COMPLETED MESSAGE#
print("Macro refresh completed!")