Run Python script through Excel VBA

Question

I have trouble running a python script from Excel VBA. This is the VBA code:

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

The python code is the following.

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

When I run VBA, the python executer opens but it immediately closes without actually executing the script. In other words, neither "hello" nor "hello, press enter" appear on the python executer window, which suddenly closes.

I'm sure the paths to the python.exe and to the python script are correct. I've tried them both with """" and with "" and in both cases it does not work. Obviously (it's super simple!) the python script works if I run it directly from the python executer.

Answer 1

You probably need a space between PythonExe & " " & PythonScript. So the command you run is "C:\…\python3.exe" "C:\…\BlackBoxAbsorbers.py" with space between the program path and the python script.

Also note that Dim PythonExe, PythonScript As String only declares PythonScript As String but PythonExe As Variant. In VBA you need to specify a type for every variable: Dim PythonExe As String, PythonScript As String or it is Variant by default!

Further objShell.Run PythonExe & PythonScript is not properly debugable. I recommend to put the command into a variable so you are able to print it in the immediate window for debugging:

Dim Command As String
Command = PythonExe & PythonScript

Debug.Print Command

objShell.Run Command 

If you do this you will see that the output of your command is:

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

And there is no space between the 2 strings, where it actually should be:

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

So I recommend:

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