需要骨架代码从 PythonWin 调用 Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/345920/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Need skeleton code to call Excel VBA from PythonWin
提问by jbrogdon
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.
我需要从 Python 脚本调用 Excel 工作簿中的 VBA 宏。其他人提供了带有宏的 Excel 工作簿。该宏从外部数据库中获取更新的值，并对数据执行一些相当复杂的处理。我需要这种按摩的结果，如果可以避免的话，我真的不想在我的 Python 脚本中复制它。所以，如果我能从我的脚本中调用宏并获取按摩结果，那就太好了。
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.
我从“Win32 上的 Python 编程”中学到的关于 COM 的所有知识。好书，但不足以完成我手头的任务。我搜索过，但没有找到任何关于如何做到这一点的好例子。有没有人有任何好的例子，或者一些关于如何寻址/调用 VBA 宏的骨架代码？Excel COM 接口上的一般参考（书籍、Web 链接等）在这里也有帮助。谢谢。
采纳答案by shahkalpesh
I am sorry, I dont know python enough. However, the following should help.
对不起，我对python还不够了解。但是，以下内容应该会有所帮助。
Excel's Application object has a Run method - which takes the name of the macro alongwith arguments to it.
Excel 的 Application 对象有一个 Run 方法——它接受宏的名称和它的参数。
Lets assume that the workbook has a macro named test.
让我们假设工作簿有一个名为 test 的宏。
Sub test(ByVal i As Integer)
MsgBox "hello world " & i
End Sub
You can call this using Application.Run "test", 1234
您可以使用 Application.Run "test", 1234 调用它
This will call the macro and show the messagebox with "hello world 1234".
这将调用宏并显示带有“hello world 1234”的消息框。
回答by jbrogdon
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.
好，我知道了！感谢您对 Application.Run 方法的帮助。此信息，以及“Microsoft Excel Visual Basic 参考”：http: //msdn.microsoft.com/en-us/library/aa209782(office.10).aspx——正如 Hammond & Robinson 在“Python Programming on Win32”——正是我们所需要的。
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
