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

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 ...")

Answer 2

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

Here is an example using Excel.