Using Python to program MS Office macros?

Question

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 1

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.

Answer 2

There is a set of cross platform Python utilities - called xlrd, xlwt, and xlutils - for reading & writing Excel files. There are some limitations (e.g. I don't think they can process macros), but they do allow you to work with Excel files on non-Windows platforms, if that's of use to you. See: http://www.python-excel.org/

Also, there are SO questions already dealing with this sort of topic, including this: Is there a better way (besides COM) to remote-control Excel?

Answer 3

Or have a look at IronPython. IPy is a native .NET implementation of Python 2.6, you can find it at http://www.codeplex.com/ironpython.

We have used it for several projects. You can use it "from the outside" using COM or - as we do - write a Excel AddIn with a ScriptHost, which calls out to IronPython code giving you an environment similar to VBA.

Being a .NET dll, IPy integrates extremely well into the modern Windows .NET stack.

Answer 4

The xlrd, xlwt, and xlutils packages mentioned above can only read and write .xls files which have size limitations of 65,000 rows and 256 columns. Aside from that it is a good tool.

But I have moved on to another python-excel package, OpenPyXL, which can read and write .xlsx files. Also I find it easy to use and the documentation is good.

OpenPyXL: http://packages.python.org/openpyxl/index.html