Running a Python from Excel Macro

Question

I have seen a couple posts instructing people on how to run a Python script through an Excel macro. I'm still having trouble with it - all I'm looking to do for the moment is see how I can get a Python script titled main.py to print 40 when I run an Excel macro. I've posted a couple filepaths below, with ellipses to replace the names of certain folders.

For background, my python.exe file is located:

R:\TRADING\...\Anders\python3.9 - Copy\3.9.7\python.exe

And the python script that I want to run is located:

R:\TRADING\...\Anders\Python\main.py

When I run print(sys.executable) on Python, it produces the location //Naeast.../.../.../.../TRADING/.../Anders/Python/main.py where //Naeast.../.../.../.../ is the location of the R: drive.

Per the response (with about 16 upvotes) to this post here, I implemented and ran this code below from my test Excel file to run main.py but it failed:

Sub RunPython1()

Dim objShell As Object
Dim PythonExe, PythonScript As String

    Set objShell = VBA.CreateObject("Wscript.Shell")

    PythonExe = """R:\TRADING\...\Anders\python3.9 - Copy\3.9.7\python.exe"""
    PythonScript = "R:\TRADING\...\Anders\Python\main.py"

    objShell.Run PythonExe & PythonScript

End Sub

I was careful to include triple quotes for PythonExe.

When I try replicating the answer to the question here, I get an error saying that the Method 'Run' of object 'IwshShell3' failed.

Answer 1

Try running Python with this test script.

Sub test()

   Const PyExe = """R:\TRADING\...\Anders\python3.9 - Copy\3.9.7\python.exe"""
   Const PyScript = "-h"
   
   Dim objShell As Object, cmd As String
   Set objShell = CreateObject("Wscript.Shell")
   
   cmd = PyExe & " " & PyScript
   Debug.Print cmd
   
   MsgBox objShell.exec(cmd).StdOut.ReadAll
   
End Sub