Accessing Microsoft Automation Objects from Python

Question

I have a set of macros that I have turned into an add-in in excel. The macros allow me to interact with another program that has what are called Microsoft Automation Objects that provide some control over what the other program does. For example, I have a filter tool in the add-in that filters the list provided by the other program to match a list in the Excel workbook. This is slow though. I might have fifty thousand lines in the other program and want to filter out all of the lines that don't match a list of three thousand lines in Excel. This type of matching takes about 30-40 minutes. I have begun wondering if there is way to do this with Python instead since I suspect the matching process could be done in seconds.

Edited:

Thanks- Based on the suggestion to look at Hammond's book I found out a number of resources. However, though I am still exploring it looks like many of these are old. For example, Hammond's book was published in 2000, which means the writing was finished almost a decade ago. Correction I just found the package called PyWin32 with a 2/2009 build.

This should get me started. Thanks

Answer 1

Mark Hammond and Andy Robinson have written the book on accessing Windows COM objects from Python.

Here is an example using Excel.

Answer 2

As far as I know it is possible to create COM objects (which is what Automation objects are) in Python on Windows. Then assuming you can get out the lists via automation it should be easy to do what you want in python.

Answer 3

However, though I am still exploring it looks like many of these are old.

COM is old. The interface hasn't changed since at least 1993.

I also don't see the package on the Python.org website. I searched for COM packages but didn't see anything useful.

http://python.net/crew/mhammond/win32/ http://sourceforge.net/projects/pywin32/

The latest update was Feb 2009, which include Python 3.0 support.

Answer 4

You will probably need the win32com package.

This is a sample exemple I found at : http://www.markcarter.me.uk/computing/python/excel.html which shows how to use com with Excel. This might be a good start.

# this example starts Excel, creates a new workbook, 
# puts some text in the first and second cell
# closes the workbook without saving the changes
# and closes Excel.  This happens really fast, so
# you may want to comment out some lines and add them
# back in one at a time ... or do the commands interactively


from win32com.client import Dispatch


xlApp = Dispatch("Excel.Application")
xlApp.Visible = 1
xlApp.Workbooks.Add()
xlApp.ActiveSheet.Cells(1,1).Value = 'Python Rules!'
xlApp.ActiveWorkbook.ActiveSheet.Cells(1,2).Value = 'Python Rules 2!'
xlApp.ActiveWorkbook.Close(SaveChanges=0) # see note 1
xlApp.Quit()
xlApp.Visible = 0 # see note 2
del xlApp

# raw_input("press Enter ...")