Using Python to program MS Office macros?

python excel vba ms-office
Jesse picture Jesse · Jan 26, 2010 · Viewed 48.4k times · Source

I've recently taken it as a project to teach myself how to program in Python. Overall, I must say that I'm impressed with it.

In the past I've typically stuck to programming in VBA mostly for MS Excel (but also a bit in MS Access and Word) and have struggled to find ways to make it do things that Python can easily do with a single command.

I was wondering if there were a reasonable way to harness the programming power and ease of Python while at the same time make use of the various tools in Office (mostly Excel)?

Answer

Jason Coon picture Jason Coon · Jan 26, 2010

Yes, absolutely. You want to use win32com module, which is part of pywin32 (get it here).

I've found you can really simplify Python integration by writing a macro in VBA for Python to use, and then just have Python call the macro. It will look something like this:

from win32com.client import Dispatch as comDispatch

xl = comDispatch('Excel.Application')
xl.Workbooks.Open("Macros.xls", False, True)
xl.Run("Macros.xls!Macro_1")

I'm sure there are plently of examples on SO... Like this one.

Related questions

How to call python script on excel vba?

trying to call a python script on Vba and I am a newb. I tried converting the main script to an exe using py2exe and then calling it from VBA (shell) but the main script calls other scripts therefore …

Read More
Creating pivot table in Excel using python causes pywintypes.com_error

I adapted the following code found here to create a pivot table in my existing excel sheet: import win32com.client as win32 win32c = win32.constants import sys import itertools tablecount = itertools.count(1) def addpivot(wb,sourcedata,title,filters=(),…

Read More
How do I call an Excel macro from Python using xlwings?

I've read the API docs for xlwings, and played around with Workbook and Sheet objects in the interpreter, but I can't figure out how to call a macro from Python. How do I use xlwings to call an Excel macro …

Read More