Need skeleton code to call Excel VBA from PythonWin
Question: I need to invoke a VBA macro within an Excel workbook from a python script.
Someone else has provided the Excel workbook with the macro. The macro grabs
updated values from an external database, and performs some fairly complex
massaging of the data. I need the results from this massaging, and I don't
really want to duplicate this in my Python script, if I can avoid it. So, it
would be great if I could just invoke the macro from my script, and grab the
massaged results.

Everything I know about COM I learned from "Python Programming on Win32". Good
book, but not enough for my task at hand. I searched, but haven't found any
good examples on how to do this. Does anyone have any good examples, or
perhaps some skeleton code of how to address/invoke the VBA macro? A general
reference (book, web link, etc) on the Excel COM interfaces would also help
here. Thanks.


Answer: OK, I got it! Thanks for the help on the Application.Run method. This info,
plus the "Microsoft Excel Visual Basic Reference":
<http://msdn.microsoft.com/en-us/library/aa209782(office.10).aspx>\--as
recommended by Hammond & Robinson in "Python Programming on Win32"--was what
was needed.

Here's the skeleton code:

    
    
    import win32com.client
    xl=win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(Filename="<your Excel File>",ReadOnly=1)
    xl.Application.Run("<your macro name>")
    #...access spreadsheet data...
    xl.Workbooks(1).Close(SaveChanges=0)
    xl.Application.Quit()
    xl=0
    

