Automating and extending the various features of Microsoft Office is often done with the Visual Basic for Applications (VBA), and in the (relatively near) future, the .NET frameworks. But thing about VBA that’s annoying is that it’s usually bound within a specific file, like the current presentation, or your personal workbook’s macros. Sharing that code with other people means passing copies of files around, and binding things to a specific file encourages fragile “look for this specific string here” type of coding.
Step in external automation. MS Office’s VBA support also allows for access through the Common Object Model (COM) interfaces. Long story short, software that can access and use COM will be able to talk to Office. Enter Python (or some other language of your choice).
First, you’ll of course need an installation of Python.
You’ll need to install the win32 extensions. Make sure that you happen to download the version that goes with your Python installtion (2.5 for 2.5, 2.6 for 2.6, etc.)
Next, you can fire up IDLE, Python’s GUI interpreter. For larger pieces of work I prefer the ipython shell, but IDLE is fine. Then you can enter the following lines to load up Excel.
1 2 3 4 5
import win32com import win32com.client xl = win32com.client.Dispatch("Excel.application") xl.visible = true
If everything went okay, then an empty instance of Excel should have appeared on your screen. What we’ve done is import the win32 COM modules (lines 1,2), created a COM dispatch object ‘xl’ that is bound to the ‘Excel.application’ in line 4 (the text is case insensitive by the way).
Finally, we reach into the xl object, and change the ‘visible’ property to true, which makes it appear. Now, we can do just about anything we can use VBA for through the Python language. The only trick is figuring out the exact syntax and quirks.
Incidentally, Powerpoint and Word (and probably the other office tools with VBA but most people are only interested in these anyways) can be accessed with:
ppt = win32com.client.Dispatch("Powerpoint.application") word = win32com.client.Dispatch("Word.application")