Execute Python code within VBA

[jhoward0101]

Hello. I am hoping to get some help to execute python code via VBA rather than executing a command batch file. I believe the reason the .py file is not executing because pathing in incorrect.
Here is the batch file that works:
C:
cd C:\users\Jim Howard\Desktop\TDDevelopment
path = %path%;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\TDApp;C:\Users\anaconda;C:\Users\anaconda\scripts;C:\Users\anaconda\Library\mingw-w64\bin;C:\Users\anaconda\Library\usr\bin;C:\Users\anaconda\Library\bin;C:\Users\anaconda\bin;C:\Users\anaconda\condabin;C:\Users\anaconda\Lib\site-packages\future\moves
erase status.txt
erase query.txt
python ordersquery.py
exit

and here is a short VBA module I am trying to use:

Sub RunPython()
Dim shell As Object
Dim exePath, scriptPath As String

Set shell = VBA.CreateObject("Wscript.shell")

spath = Environ$("systemroot\System32\" & ";" & "systemroot\System32\Wbem" & ";" & "systemroot\System32\WindowsPowerShell\v1.0\" & ";C:\TDApp;C:\Users\anaconda;C:\Users\anaconda\scripts;C:\Users\anaconda\Library\mingw-w64\bin;C:\Users\anaconda\Library\usr\bin;C:\Users\anaconda\Library\bin;C:\Users\anaconda\bin;C:\Users\anaconda\condabin;C:\Users\anaconda\Lib\site-packages\future\moves")

exePath = """C:\Users\Jim Howard\python.exe"""

scriptPath = "C:\Desktop\Tddevelopment\ordersquery.py"

shell.Run exePath & scriptPath

End Sub

I left the commented spath statement in to see what I have tried but it does not execute the .py flle, it also does not fail with an error, so I think if i could get the pathing right, it would work. I think python is being executed from anaconda with the batch file.

Any help would be much appreciated.

Thanks in advance,
James Howard

 

[rlv01]

How about this.

Sub RunPython()
    Dim exePath As String, scriptPath As String, ShellToPythonCmd As String

    exePath = "C:\Users\Jim Howard\python.exe"
    scriptPath = "C:\Desktop\Tddevelopment\ordersquery.py"
    ShellToPythonCmd = exePath & " " & scriptPath

    With VBA.CreateObject("Wscript.shell")
        .Run ShellToPythonCmd
    End With
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

as it makes the code easier to read.)

 

[jhoward0101]

How about this.

Sub RunPython()
    Dim exePath As String, scriptPath As String, ShellToPythonCmd As String

    exePath = "C:\Users\Jim Howard\python.exe"
    scriptPath = "C:\Desktop\Tddevelopment\ordersquery.py"
    ShellToPythonCmd = exePath & " " & scriptPath

    With VBA.CreateObject("Wscript.shell")
        .Run ShellToPythonCmd
    End With
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

as it makes the code easier to read.)

Thanks for your reply rlv01.
When running the module I receive a Run Time error -'2147024898(80070002)': "Method of 'Run' of object 'IWshShell3' failed.

 

[rlv01]

The most likely explanation is that your exePath is not correct. In other words

    exePath = "C:\Users\Jim Howard\python.exe"

There is no file python.exe in that folder (C:\Users\Jim Howard\)

 

[jhoward0101]

Was able to get it to work. Thanks for your help