2

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.

shruti1810
  • 3,920
  • 2
  • 16
  • 28
jbrogdon
  • 671
  • 1
  • 7
  • 15

2 Answers2

6

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
jbrogdon
  • 671
  • 1
  • 7
  • 15
1

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

shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • Thanks. That helps some. From the pywin32 side, I'm still looking for something that shows the initial steps for opening the file, and referencing the complete interface. PyWin32 shows several parts of the interface hierarchy that support the Run method. – jbrogdon Dec 06 '08 at 07:08