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

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 3

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: http://stackoverflow.com/questions/528817/is-there-a-better-way-besides-com-to-remote-control-excel

Answer 4

Have a look at this book, "Python Programming On Win32". It really tells you everything you need to know. And it's a good book. http://oreilly.com/catalog/9781565926219