通过excelvba运行Python脚本

Question

我无法从Excel VBA运行python脚本。这是VBA代码：

Sub RunPython()
    
    Dim objShell As Object
    Dim PythonExe, PythonScript As String
    Set objShell = VBA.CreateObject("Wscript.Shell")
    PythonExe = """C:\Users\carlo\AppData\Local\Microsoft\WindowsApps\python3.exe""" 
    PythonScript = """C:\Users\carlo\Desktop\MSc_Thesis\ML applications\BlackBox\BlackBoxAbsorbers.py"""  
    objShell.Run PythonExe & PythonScript

End Sub

python代码如下所示

print("hello")
input("hello, press enter")

当我运行VBA时，python executer会打开，但会立即关闭，而不会实际执行脚本。换句话说，python executer窗口中既没有"hello"也没有"hello, press enter"出现，该窗口会突然关闭

我确信python.exe和python脚本的路径是正确的。我用""""和""都试过了，但在这两种情况下都不起作用。显然（非常简单！）如果我直接从python执行器运行python脚本，它就可以工作

Answer 1

解决了！！我不明白为什么，但是拆分objShell。运行PythonExe和objShell。运行PythonScript它工作得很好！就像这样：

Public Sub RunPython()    
    Dim objShell As Object
    Set objShell = VBA.CreateObject("Wscript.Shell")

    Dim PythonExe As String
    PythonExe = """C:\Users\carlo\AppData\Local\Microsoft\WindowsApps\python3.exe""" 

    Dim PythonScript As String
    PythonScript = """C:\Users\carlo\Desktop\MSc_Thesis\ML applications\BlackBox\BlackBoxAbsorbers.py"""  
    
     objShell.Run PythonExe
     objShell.Run PythonScript

End Sub

无论如何谢谢@pᴇʜ，我认为你的回答是肯定的，因为你一直都很好

Answer 2

您可能需要在PythonExe & " " & PythonScript之间留一个空格。因此，您运行的命令是"C:\…\python3.exe" "C:\…\BlackBoxAbsorbers.py"，程序路径和python脚本之间有空格

还请注意Dim PythonExe, PythonScript As String仅声明PythonScript As String，但PythonExe As Variant。在VBA中，您需要为每个变量指定一个类型：Dim PythonExe As String, PythonScript As String或者默认为Variant

此外objShell.Run PythonExe & PythonScript无法正确调试。我建议将命令放入变量中，以便您能够在即时窗口中打印它以进行调试：

Dim Command As String
Command = PythonExe & PythonScript

Debug.Print Command

objShell.Run Command 

如果执行此操作，您将看到命令的输出为：

"C:\Users\carlo\AppData\Local\Microsoft\WindowsApps\python3.exe""C:\Users\carlo\Desktop\MSc_Thesis\ML applications\BlackBox\BlackBoxAbsorbers.py"

两个字符串之间没有空格，实际上应该是：

"C:\Users\carlo\AppData\Local\Microsoft\WindowsApps\python3.exe" "C:\Users\carlo\Desktop\MSc_Thesis\ML applications\BlackBox\BlackBoxAbsorbers.py"

因此，我建议：

Public Sub RunPython()    
    Dim objShell As Object
    Set objShell = VBA.CreateObject("Wscript.Shell")

    Dim PythonExe As String
    PythonExe = """C:\Users\carlo\AppData\Local\Microsoft\WindowsApps\python3.exe""" 

    Dim PythonScript As String
    PythonScript = """C:\Users\carlo\Desktop\MSc_Thesis\ML applications\BlackBox\BlackBoxAbsorbers.py"""  

    Dim Command As String
    Command = PythonExe & " " & PythonScript

    objShell.Run Command
End Sub