CODEWITHSUNDEEP
pythonexcelvbachdir
aadya bajaj
aadya bajaj

Reputation: 31

Calling Python Script from VBA - Not working

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

Upvotes: 2

Views: 1844

Answers (2)

ahmedul Kabir Omi
ahmedul Kabir Omi

Reputation: 144

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

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166341

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.

Upvotes: 1

Related Questions