A handy feature in Excel is the ability to quickly record a sequence of operations into a Visual Basic (VB) macro. It’s also fairly simple to take a captured VB macro, change it slightly, and use it in your Python scripts. I’ve used this capability dozens of times over the years to automate spreadsheet calculations and pivot table generation. I now have a good understanding of how to port the VB macro into Python; let me share the technique with you.
In this post, I’ll capture a simple set of operations as a Visual Basic macro, examine the macro, and port it to Python. I’m using the MultiplicationTable.xlsx file as a starting point, it’s a simple 10×10 multiplication table that will be expanded and reformatted. You can create this table yourself or download the file from https://github.com/pythonexcels/examples/raw/master/MultiplicationTable.xlsx
Enabling the Developer Tab
The first step is to capture the macro in Excel by using the Record Macro feature in Excel. The Record Macro button is located in the Developer tab, which might be disabled in your application. To enable the Developer tab,
Click the File tab
Click Options, then click “Customize Ribbon”
In the “Customize the Ribbon” section, select “Main Tabs”, enable the Developer option in the list, and click OK
In older versions of Excel,
Select “Excel Options” from the ribbon menu
Select “Popular” in the left column
Enable the “Show Developer tab in the Ribbon” option and click OK
Recording a Macro
Now you are ready to record a macro. Starting with a simple spreadsheet containing a table of data, click on the “Developer” tab, then “Record Macro”.
The goal is to expand the existing table to a 15×15 table, adjust the column width to make the table appear more proportional, and save the new spreadsheet. Now that the macro is recording, the first step is to select the last row of data and expand it by dragging it down an additional five rows. First, select the data:
then drag the mouse to create five new rows of data.
Using the same technique, select the last column of data and create five new columns.
Now you have a 15×15 multiplication table. To resize the columns, select the headers for columns B through P, click the right mouse and select “Column Width”.
Enter “4” as the new column width and click OK. The spreadsheet now looks like this:
Now stop capturing the macro by clicking the “Stop Recording” button.
To view the macro, click the Macros (View Macros) button.
Select the macro you just recorded and click Edit. The macro should be named Macro1. If you were experimenting, you will have more than one macro in the list and should select the highest numbered macro.
The tool opens your macro in the Microsoft Visual Basic Integrated Development Environment (IDE). Your macro should look similar to the following macro:
Sub Macro1() ' ' Macro1 Macro ' ' Range("B11:K11").Select Selection.AutoFill Destination:=Range("B11:K16"), Type:=xlFillDefault Range("B11:K16").Select Range("K2:K16").Select Selection.AutoFill Destination:=Range("K2:P16"), Type:=xlFillDefault Range("K2:P16").Select Columns("B:P").Select Selection.ColumnWidth = 4 End Sub
Don’t worry if there are some extra or redundant lines in your macro, they can be removed as the script is ported.
Next, save your new spreadsheet as “MultiplicationTable15.xlsx”, but don’t close Excel. We’re now ready to start Python and port the VB macro.
Porting from Visual Basic to Python
To get started, open the Python Integrated Development Environment (IDLE), and open the spreadsheet with the original 10×10 multiplication table by entering the following four commands. Make sure the “MultiplicationTable.xlsx” spreadsheet is in your My Documents folder.
import win32com.client as win32 excel = win32.gencache.EnsureDispatch('Excel.Application') wb = excel.Workbooks.Open('MultiplicationTable.xlsx') excel.Visible = True
Your screen should now look like this:
In this example, the first command,
import win32com.client as
win32, imports the win32 module. The next statement,
win32.gencache.EnsureDispatch('Excel.Application'), attaches to a
running Excel process or opens a new Excel process if needed. The
wb = excel.Workbooks.Open('MultiplicationTable.xlsx')
opens the worksheet. In general, you’ll need to run
excel.Workbooks.Add() to open an
existing Excel file or create a new workbook. The command
excel.Visible = True makes Excel visible on the screen.
Looking at the Macro1 macro, the first command is
Range("B11:K11").Select. The Range method is within the
context of the Worksheet, so you need to create an object that points
to the worksheet. The command
ws = wb.Worksheets('Sheet1') will do
If you noticed, I made a typo when entering the command and typed
Worksheets. Don’t panic if you make a mistake as I did; in most cases you can simply retype the correct command and continue on.
After you’ve created the
ws object to reference the worksheet,
Range command to
ws. and try it. Note that in Python,
Select is a function and requires the open and close parenthesis
pair in order to operate correctly. This pattern may be used for every
Range().Select line in the original VB macro.
ws.Range("B11:K11").Select() at the prompt in the IDLE window,
then bring the worksheet to the foreground. Confirm that range B11:K11
has been selected as shown in the following figure.
The next task is to autofill the five rows below the existing table
by using the
Type:=xlFillDefault VB command.
Selection is a method at the Excel
application level, so you need to prefix the command with
must be provided to the function with the keyword arguments
Type or by using positional notation as I’ve done
in this example.
Specifying Visual Basic Constant Values in a Python Script
There are two ways to provide a constant value such as
xlFillDefault: 1) specify the constant name or 2) specify the value
of the constant. To use the constant name in a Python program, prefix
the name with
win32.constants. In this example,
Visual Basic becomes
win32.constants.xlFillDefault in Python.
Alternatively, you can use the Visual Basic IDE to display the value of the constant and use the value in your Python program. Click the constant in the IDE and choose Quick Info from the context menu. The IDE displays a tooltip with the constant value as shown below:
I’ve seen many examples where the developer replaces the constant with the actual value (0 in this case). My preference is to avoid replacing Excel constants with numbers in my scripts; I believe that including the constant names increases the clarity of the script.
Finishing the Script
Combining these translations, the full Python command is
ws.Range("B11:K16"), win32.constants.xlFillDefault) as I’ve used in the example.
Occasionally you’ll make a mistake when capturing a macro and record
extra, unnecessary commands. At the same time, the macro recorder
might insert additional commands that aren’t needed in the Python
script. In this case, the command
needed and can be ignored. The next two macro commands,
Type:=xlFillDefault, are translated in the same way as the
AutoFill commands discussed earlier.
The multiplication table is now expanded to the full 15×15 table as follows:
The next section of the macro selects columns B through P and sets
their width to 4 characters. The statement
Columns("B:P").Select is a
property of the worksheet, so prefix it with the
and add the parenthesis to make it a Python function call. In the next
Selection is a property of excel, so insert the
excel. prefix. The translated statements are shown below.
The Excel spreadsheet is now complete; the multiplication table has
been expanded to 15×15 and the column widths have been set to four
characters. At this point, translation of the macro is complete, but
the modified spreadsheet must be saved. To write the file and quit
Excel, use the
Quit methods as shown below.
For your reference, here is the complete Python script, also available at https://github.com/pythonexcels/examples/raw/master/make15x15.py
# # make15x15.py # Expand an existing 10x10 multiplication table and resize columns # import win32com.client as win32 excel = win32.gencache.EnsureDispatch('Excel.Application') wb = excel.Workbooks.Open('MultiplicationTable.xlsx') excel.Visible = True ws = wb.Worksheets('Sheet1') ws.Range("B11:K11").Select() excel.Selection.AutoFill(ws.Range("B11:K16"),win32.constants.xlFillDefault) ws.Range("K2:K16").Select() excel.Selection.AutoFill(ws.Range("K2:P16"),win32.constants.xlFillDefault) ws.Columns("B:P").Select() excel.Selection.ColumnWidth = 4 wb.SaveAs('NewMultiplicationTable.xlsx') excel.Application.Quit()
If this is the first time you’ve ported an Excel macro from VB to Python, congratulations! Please note that in this example, things are kept simple and there is absolutely no error checking or exception handling used. Normally you would need to provide at least a minimal level of error checking and exception handling in your script so that common errors (missing input file, can’t invoke Excel, etc) are caught and handled nicely. This script was validated in Excel 2017, but should run without issue in older versions.
Some Porting Guidelines
- Prefix the
Range().Selectstatements with the object name pointing to the worksheet (
wsin this example)
()when porting a method from VB to Python
- Prefix the
Selectionstatements with the object name for the Excel spreadsheet (
excelin this example)
- Prefix the
Columnsstatements with the object name for the worksheet (
wsin this example)
Porting Reference Table for this example
Note that I didn’t capture the
Workbooks.SaveAs lines in the VB script, it’s left as an exercise
for the reader to research those commands.
Python (refer to http://www.python.org)
pywin32 Python module https://pypi.org/project/pywin32
Microsoft Excel (refer to http://office.microsoft.com/excel)
Source Files and Scripts
Source for the program make15x15.py and data text file are available at http://github.com/pythonexcels/examples
Originally posted on October 12, 2009 / Updated September 27, 2019