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.
I am sorry, I dont know python enough. However, the following should help.
Excel's Application object has a Run method - which takes the name of the macro alongwith arguments to it.
Lets assume that the workbook has a macro named test.
Sub test(ByVal i As Integer)
MsgBox "hello world " & i
End Sub
You can call this using Application.Run "test", 1234
This will call the macro and show the messagebox with "hello world 1234".