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
– Tim Roberts, Commented Aug 31, 2022 at 20:05
Try this method if you want the window to stay open after you've called your script - stackoverflow.com/a/70268284/478884 That may help you see any errors which might be getting generated by the script. — Tim Williams
– Tim Williams, Commented Aug 31, 2022 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
– aadya bajaj, Commented Sep 1, 2022 at 2:22

Tim Williams · Accepted Answer · 2022-09-01 06:13:57Z

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 1, 2022 at 6:13

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aadya bajaj Over a year ago

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 Over a year ago

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 - stackoverflow.com/questions/65348890/… Followed Maninder's and Farhad's response there.

ahmedul Kabir Omi · Accepted Answer · 2022-09-28 17:56:30Z

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

Collectives™ on Stack Overflow

Calling Python Script from VBA - Not working

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related