如何在excel vba上调用python脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18135551/
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
How to call python script on excel vba?
提问by Ege Ozlem
trying to call a python script on Vba and I am a newb. I tried converting the main script to an exe using py2exe and then calling it from VBA (shell) but the main script calls other scripts therefore it becomes complicated and I messed it up (my exe is not functional). Besides, the the main script is a large file and I do not want to revise it a lot.
试图在 Vba 上调用 python 脚本，我是新手。我尝试使用 py2exe 将主脚本转换为 exe，然后从 VBA（shell）调用它，但主脚本调用其他脚本，因此它变得复杂，我搞砸了（我的 exe 不起作用）。此外，主脚本是一个大文件，我不想对其进行太多修改。
Bottomline, is there a way to call the main script from excel vba, without converting the script to an exe file.
底线，有没有办法从 excel vba 调用主脚本，而无需将脚本转换为 exe 文件。
So far, I tried:
到目前为止，我尝试过：
RetVal = Shell("C:\python27\python.exe " & "import " & "C:\" & "MainScriptFile")
It starts python.exe but does nothing else. Then I tried:
它启动 python.exe 但不执行任何其他操作。然后我尝试：
RetVal = Shell("C:\Windows\System32\cmd.exe " & "python " & "C:\Python27\hello.py")
It starts command prompt but does not even start python.
它启动命令提示符，但甚至不启动 python。
P.S. I checked all the related questions in the forum, they do not solve my prob.
PS我检查了论坛中的所有相关问题，它们没有解决我的问题。
采纳答案by Rajgopal C
Try this:
尝试这个：
RetVal = Shell("<full path to python.exe> " & "<full path to your python script>")
Or if the python script is in the same folder as the workbook, then you can try :
或者，如果 python 脚本与工作簿在同一个文件夹中，那么您可以尝试：
RetVal = Shell("<full path to python.exe> " & ActiveWorkBook.Path & "\<python script name>")
All details within <>are to be given. <>- indicates changeable fields
将提供其中的所有详细信息<>。<>- 表示可变字段
I guess this should work. But then again, if your script is going to call other files which are in different folders, it can cause errors unless your script has properly handled it. Hope it helps.
我想这应该有效。但是话又说回来，如果您的脚本要调用位于不同文件夹中的其他文件，则除非您的脚本正确处理它，否则它可能会导致错误。希望能帮助到你。
回答by Brad
There are a couple of ways to solve this problem
有几种方法可以解决这个问题
Pyinx - a pretty lightweight tool that allows you to call Python from withing the excel process space http://code.google.com/p/pyinex/
Pyinx - 一个非常轻量级的工具，允许您从 excel 进程空间调用 Python http://code.google.com/p/pyinex/
I've used this one a few years ago (back when it was being actively developed) and it worked quite well
几年前我用过这个（回到它正在积极开发的时候）并且它运行得很好
If you don't mind paying, this looks pretty good
如果你不介意付钱，这看起来很不错
https://datanitro.com/product.html
https://datanitro.com/product.html
I've never used it though
虽然我从来没有用过
Though if you are already writting in Python, maybe you could drop excel entirely and do everything in pure python? It's a lot easier to maintain one code base (python) rather than 2 (python + whatever excel overlay you have).
虽然如果你已经在用 Python 编写，也许你可以完全放弃 excel 并用纯 Python 做所有事情？维护一个代码库 (python) 比维护 2 个代码库要容易得多（python + 你拥有的任何 excel 覆盖）。
If you really have to output your data into excel there are even some pretty good tools for that in Python. If that may work better let me know and I'll get the links.
如果您真的必须将数据输出到 excel 中，那么 Python 中甚至还有一些非常好的工具。如果这可以更好地工作，请告诉我，我会得到链接。
回答by ehremo
You can also try ExcelPythonwhich allows you to manipulate Python object and call code from VBA.
您还可以尝试ExcelPython，它允许您操作 Python 对象并从 VBA 调用代码。
回答by T Roc
Try this:
尝试这个：
retVal = Shell("python.exe <full path to your python script>", vbNormalFocus)
replace <full path to your python script>with the full path
替换<full path to your python script>为完整路径
回答by Pablo Vilas
This code will works:
此代码将起作用：
your_path= ActiveWorkbook.Path & "\your_python_file.py" Shell "RunDll32.Exe Url.Dll,FileProtocolHandler " & your_path, vbNormalFocus ActiveWorkbook.Path return the current directory of the workbook. The shell command open the file through the shell of Windows.
ActiveWorkbook.Path 返回工作簿的当前目录。shell 命令通过 Windows 的 shell 打开文件。
回答by Omi
To those who are stuck wondering why a window flashes and goes away without doing anything the python script is meant to do after calling the shell command from VBA: In my program
对于那些想知道为什么窗口闪烁并消失而不做任何事情的人来说，python 脚本在从 VBA 调用 shell 命令后打算做的：在我的程序中
Sub runpython()
Dim Ret_Val
args = """F:\my folder\helloworld.py"""
Ret_Val = Shell("C:\Users\username\AppData\Local\Programs\Python\Python36\python.exe " & " " & args, vbNormalFocus)
If Ret_Val = 0 Then
MsgBox "Couldn't run python script!", vbOKOnly
End If
End Sub
In the line args = """F:\my folder\helloworld.py""", I had to use triple quotes for this to work. If I use just regular quotes like: args = "F:\my folder\helloworld.py" the program would not work. The reason for this is that there is a space in the path (my folder). If there is a space in the path, in VBA, you need to use triple quotes.
在 args = """F:\my folder\helloworld.py""" 行中，我必须使用三重引号才能工作。如果我只使用常规引号，例如： args = "F:\my folder\helloworld.py" 程序将无法运行。原因是路径（我的文件夹）中有空格。如果路径中有空格，在VBA中，需要使用三引号。
回答by Saul Han
To those who are stuck wondering why a window flashes and goes away without doing anything, the problem may related to the RELATIVE path in your Python script. e.g. you used ".\". Even the Python script and Excel Workbook is in the same directory, the Current Directory may still be different. If you don't want to modify your code to change it to an absolute path. Just change your current Excel directory before you run the python script by:
对于那些想知道为什么窗口闪烁并没有做任何事情就消失的人来说，问题可能与 Python 脚本中的 RELATIVE 路径有关。例如，您使用了“.\”。即使 Python 脚本和 Excel 工作簿在同一目录中，当前目录仍然可能不同。如果您不想修改代码以将其更改为绝对路径。只需在运行 python 脚本之前更改当前的 Excel 目录：
ChDir ActiveWorkbook.Path
I'm just giving a example here. If the flash do appear, one of the first issues to check is the Current Working Directory.
我这里只是举个例子。如果闪存确实出现，首先要检查的问题之一是当前工作目录。
回答by ASH
I just came across this old post. Nothing listed above actually worked for me. I tested the script below, and it worked fine on my system. Sharing here, for the benefit of others who come here after me.
我刚看到这个旧帖子。上面列出的任何内容都没有对我有用。我测试了下面的脚本，它在我的系统上运行良好。在这里分享，为了其他跟随我来到这里的人的利益。
Sub RunPython()
Dim objShell As Object
Dim PythonExe, PythonScript As String
Set objShell = VBA.CreateObject("Wscript.Shell")
PythonExe = """C:\your_path\Python\Python38\python.exe"""
PythonScript = "C:\your_path\from_vba.py"
objShell.Run PythonExe & PythonScript
End Sub
