Geek Answers Handbook

Need skeleton code to call Excel VBA from PythonWin

I need to call a VBA macro in an Excel workbook from a python script. Someone else provided an Excel workbook with a macro. The macro retrieves updated values ​​from an external database and performs quite complex data massaging. I need the results of this massage, and I don't want to duplicate this in my Python script if I can avoid it. So, it would be great if I could just call the macro from my script and capture the massed results.

Everything that I know about COM, I learned from "Python Programming on Win32". A good book, but not enough for my task. I searched, but did not find good examples of how to do this. Does anyone have any good examples or maybe some skeletal code of how to handle / call a VBA macro? A general link (book, web link, etc.) to Excel COM interfaces will also help here. Thanks.

+2
source share
2 answers

Sorry, I don't know python enough. However, the following should help.

An Excel application object has a Run method that takes a macro name along with arguments to it.

Suppose the book has a macro called test.

Sub test(ByVal i As Integer) MsgBox "hello world " & i End Sub

You can call this using Application.Run "test", 1234

This will invoke the macro and display a message with "hello world 1234".

0
source

OK I understood! Thanks for the help in the Application.Run method. This information, plus the "Microsoft Visual Visual Basic Reference": http://msdn.microsoft.com/en-us/library/aa209782(office.10).aspx - recommended by Hammond and Robinson in "Programming Python on Win32" - It was what was needed.

Here's the skeletal 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
+6
source

More articles:


All Articles