Calling Python Script from VBA - Not working

Question

I referred to the answers given here - How to call python script on excel vba? But it didn't work for me.

I simply see a screen flash before me and the Python script simply does not run. The Python Script I am writing is in Spyder, and has multiple modules referenced to it.

This is the code I am writing :

Sub RunPythonScript()

Dim objShell As Object
Dim PythonExePath As String, PythonScriptPath As String
ActiveWorkbook.Save
ChDir "C:\path\where\.py\file\is\saved"

    Set objShell = VBA.CreateObject("Wscript.Shell")
    
    PythonExePath = """C:\path\where\python.exe\exists"""
    PythonScriptPath = """C:\path\where\.py\file\is\saved"""

    
    objShell.Run PythonExePath & PythonScriptPath
    Application.Goto Reference:="RunPythonScript"
    
    objShell.Popup "Your results are now processed", , "Attention!"


End Sub

Typo. You need a space between the exe name and the script name. `PythonExePath & " " & PythonScriptPath`. — Tim Roberts, Aug 31 '22 at 20:05
Try this method if you want the window to stay open after you've called your script - https://stackoverflow.com/a/70268284/478884 That may help you see any errors which might be getting generated by the script. — Tim Williams, Aug 31 '22 at 20:07
Hi @TimWilliams , Do you mean editing the code to look like this: '''Dim objShell As Object Dim PythonExePath As String, PythonScriptPath As String ActiveWorkbook.Save ChDir ActiveWorkbook.Path Set objShell = VBA.CreateObject("Wscript.Shell") PythonExePath = """pathto\python.exe""" PythonScriptPath = """pathtopythonfile\xyz.py""" Shell """cmd.exe /k""" & PythonExePath & """ """ & PythonScriptPath & """ """, vbNormalFocus 'objShell.Run PythonExePath & """ """ & PythonScriptPath - commenting this out instead.''' — aadya bajaj, Sep 01 '22 at 02:22

score 1 · Answer 1 · answered Sep 01 '22 at 06:13

1

Try like this:

Sub RunPythonScript()

    Dim exe, pth
    
    exe = "C:\path\where\python.exe\exists"
    pth = "C:\path\where\.py\file\is\saved"
    
    Shell "cmd.exe /k """"" & exe & """ """ & pth & """""", vbNormalFocus

    MsgBox "Your results are now processed", vbInformation, "Attention!"

End Sub

https://www.myonlinetraininghub.com/vba-shell

I don't know what your Application.Goto is for.

answered Sep 01 '22 at 06:13

Tim Williams

154,628
8
97
125

Thanks a lot @TimWilliams for the prompt response! I am now able to see the command prompt as it stays on screen instead of flashing and minimizing. But there is no error displayed there. It is blank. And the Python script is still not running... So still not sure what is happening. – aadya bajaj Sep 02 '22 at 03:08
But just to mention for completeness - when I first ran your code - I got this error - "Python was not found run without arguments to install from Microsoft"... So I followed instructions from this answer - https://stackoverflow.com/questions/65348890/python-was-not-found-run-without-arguments-to-install-from-the-microsoft-store Followed Maninder's and Farhad's response there. – aadya bajaj Sep 02 '22 at 03:11

score 1 · Answer 2 · answered Sep 28 '22 at 17:56

run extremal script like python using shell command & get the output as a string. i am using it for datetime type data using python datetime method from vba.

Public Function ShellRun(sCmd As String) As String
    'sCmd = "python.exe test.py arg" as example
    'Run a python in shell command & returning the output as a string
    
    Dim osh As Object
    Dim oEx As Object
    Dim oOp As Object
    Dim s As String
    Dim sLine As String
    
    Set osh = CreateObject("WScript.Shell")
    Set oEx = osh.Exec(sCmd)
    Set oOp = oEx.Stdout

    'reed output line from oOp (StdOut object)
    While Not oOp.atEndOfStream
        sLine = oOp.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    ShellRun = s

End Function

Calling Python Script from VBA - Not working

2 Answers2

Linked