--- title: "Red Oak Strategic - How To: Add & Execute Python Excel Macro" description: Our team designed a method to add and execute Python Excel macro, useful for applications combining Pandas and Microsoft Excel. --- [Data](https://blog.redoakstrategic.com/tag/data), [R](https://blog.redoakstrategic.com/tag/r), [Code](https://blog.redoakstrategic.com/tag/code), [Excel](https://blog.redoakstrategic.com/tag/excel), [Data Science](https://blog.redoakstrategic.com/tag/data-science), [Python](https://blog.redoakstrategic.com/tag/python), [Tutorials](https://blog.redoakstrategic.com/tag/tutorials) # Red Oak Strategic - How To: Add & Execute Python Excel Macro ![Mark Stephenson](https://blog.redoakstrategic.com/hubfs/mark_final.jpg) [ Mark Stephenson ](https://blog.redoakstrategic.com/author/mark-stephenson) Thu, 14 December 2017 #### Table of Contents #### Need Help? Get in Touch! [![](https://blog.redoakstrategic.com/hs-fs/hubfs/Imported_Blog_Media/Sin-t%C3%ADtulo-1-04-420x280_c.jpg?width=420&height=280&name=Sin-t%C3%ADtulo-1-04-420x280_c.jpg "Sin título-1-04")](https://blog.redoakstrategic.com/hubfs/Imported_Blog_Media/Sin-t%C3%ADtulo-1-04.jpg) 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](https://github.com/PyMySQL/PyMySQL) 3. win32com, used to refresh our macros:  [PyWin32 – Windows](https://sourceforge.net/projects/pywin32/) 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](http://xlsxwriter.readthedocs.io/working_with_macros.html) 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!") ```   --- ![](https://blog.redoakstrategic.com/hs-fs/hubfs/Imported_Blog_Media/redoaklogovertical-e1509733395364-Jan-19-2022-06-17-02-63-PM.png?width=200&height=258&name=redoaklogovertical-e1509733395364-Jan-19-2022-06-17-02-63-PM.png) ## Contact Red Oak Strategic From cloud migrations to machine learning & AI - maximize your data and analytics capabilities with support from an AWS Advanced Tier consulting partner. ## Related Posts [![](https://blog.redoakstrategic.com/hubfs/IMG_1520%20(1).png) ](https://blog.redoakstrategic.com/aws-summit-dc-2026-reflection) Analytics ### [AWS Summit DC 2026 Reflection](https://blog.redoakstrategic.com/aws-summit-dc-2026-reflection) AWS Summit 2026 Reflection: From Possibility to Production Execution Last year at AWS Summit in Washington, DC, the AI conversation still ha... ![Tyler Sanders](https://blog.redoakstrategic.com/hubfs/TylerHeadshot.jpg) Tyler Sanders Thu, 2 July 2026 [ Read More ](https://blog.redoakstrategic.com/aws-summit-dc-2026-reflection) [![](https://blog.redoakstrategic.com/hubfs/pexels-markusspiske-1972464.jpg) ](https://blog.redoakstrategic.com/a-serverless-aws-pattern-for-managing-etl-logging-at-scale-part-1) Data ### [A Serverless AWS Pattern for Managing ETL Logging at Scale - Part 1](https://blog.redoakstrategic.com/a-serverless-aws-pattern-for-managing-etl-logging-at-scale-part-1) Introduction This is the first installment of a three-part technical series detailing the end-to-end observability and log tracking architec... ![Raman Kadariya](https://blog.redoakstrategic.com/hubfs/1759939588766.jpeg) Raman Kadariya Fri, 26 June 2026 [ Read More ](https://blog.redoakstrategic.com/a-serverless-aws-pattern-for-managing-etl-logging-at-scale-part-1) [![](https://blog.redoakstrategic.com/hubfs/pexels-kindelmedia-8566629.jpg) ](https://blog.redoakstrategic.com/ai-readiness-to-adoption-accelerating-the-path-from-poc-to-production) Data ### [AI Readiness to Adoption: Accelerating the Path from POC to Production](https://blog.redoakstrategic.com/ai-readiness-to-adoption-accelerating-the-path-from-poc-to-production) The Problem: AI Pilots Are Easy to Start and Hard to Scale AI is no longer a future-state conversation for most organizations. Leadership te... ![Patrick Stewart](https://app.hubspot.com/settings/avatar/f5bcd7d312e4326bccc05f975f9c657c) Patrick Stewart Thu, 11 June 2026 [ Read More ](https://blog.redoakstrategic.com/ai-readiness-to-adoption-accelerating-the-path-from-poc-to-production) ## Ready to get started? --- Kickstart your cloud and data transformation journey with a complimentary conversation with the Red Oak team. --- [ BEGIN YOUR JOURNEY ](https://www.redoakstrategic.com/contact?hsLang=en) ```json { "@context" : "http://schema.org", "@type" : "Article", "author" : { "@type" : "Person", "name" : [ "Mark Stephenson" ], "url" : "https://blog.redoakstrategic.com/author/mark-stephenson" }, "datePublished" : "2017-12-14", "description" : "Our team designed a method to add and execute Python Excel macro, useful for applications combining Pandas and Microsoft Excel.", "headline" : "Red Oak Strategic - How To: Add & Execute Python Excel Macro", "image" : "", "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "url" : "" }, "name" : "Red Oak Strategic" }, "url" : "https://blog.redoakstrategic.com/pythonexcelmacro" } ```