Red Oak Strategic
  • Home
  • About Us
  • Services
  • Amazon Web Services
    • Database Engineering
    • Machine Learning and AI
  • Resources
    • Blog
    • Case Studies
Contact Us
  • Home
  • About Us
  • Services
  • Amazon Web Services
    • Database Engineering
    • Machine Learning and AI
  • Resources
    • Blog
    • Case Studies
Mark Stephenson
Thursday, 14 December 2017 / Published in Data, R, Code, Excel, Data Science, Python, Tutorials

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!")

 


  • Tweet
Tagged under: Data R Code Excel Data Science Python Tutorials

What you can read next

Business Intelligence Across a Private Equity Portfolio
Data Visualization: Empowering Decision Makers
Tracking Coronavirus: Building Parameterized Reports to Analyze Changing Data Sources

Leave a reply

    Recent Posts

    • Business Intelligence Across a Private Equity Portfolio

      Background Recognizing an opportunity to expand...
    • Data Visualization: Empowering Decision Makers

      Time and again, across Red Oak Strategic’s...
    • Tracking Coronavirus: Building Parameterized Reports to Analyze Changing Data Sources

      The pace of our modern world, and the impressive...
    • Draw Rotatable 3D Charts in R Shiny with Highcharts and JQuery

      While it might be tempting to liven up a report...
    • Customizing Click Events: How to Capture and Store Data from JavaScript Objects in R Variables

      Interaction Design for Data Exploration...

    Categories

    • 2016 Election (6)
    • Analytics (11)
    • Apache Spark (1)
    • Blockchain (1)
    • Business Intelligence (1)
    • Case Studies (3)
    • Code (12)
    • Data (14)
    • Data Processing (2)
    • Data Science (18)
    • Data Visualization (8)
    • Databases (1)
    • Donald Trump (1)
    • Excel (1)
    • Exploratory Data Science (1)
    • Financial Analytics (1)
    • Forecasting (1)
    • ggplot2 (1)
    • h2o (1)
    • Highcharts (1)
    • Hillary Clinton (1)
    • JavaScript (3)
    • JQuery (1)
    • Machine Learning (3)
    • Maps (1)
    • Political Analytics (3)
    • Politics (7)
    • Polling (3)
    • Predictive Analytics (3)
    • Private Equity (1)
    • Python (2)
    • Python 3 (1)
    • R (10)
    • R Shiny (4)
    • RegEx (1)
    • RShiny (2)
    • Sparkling Water (1)
    • Time Series (1)
    • Tutorial (1)
    • Tutorials (8)
    • Uber (1)
    see all topics

    © 2022 Red Oak Strategic

    KEEP UPDATED

    Receive our updates, best practices and latest news straight to your inbox