Return result from Python to Vba

Question

I'm using a VBA code that calls a python script. I can send a parameter to my python script and reading it using sys.argv[1].

In the python code I have a function that takes the given argument and return a value.

Please how can I get the return value in VBA?

Answer 1

Consider using VBA Shell's StdOut to capture a stream of the output lines. Be sure to have the Python script print to screen the value:

Python

...
print(outputval)

VBA (s below would be string output)

Public Sub PythonOutput()

    Dim oShell As Object, oCmd As String
    Dim oExec As Object, oOutput As Object
    Dim arg As Variant
    Dim s As String, sLine As String

    Set oShell = CreateObject("WScript.Shell")
    arg = "somevalue"
    oCmd = "python ""C:\Path\To\Python\Script.py""" & " " & arg

    Set oExec = oShell.Exec(oCmd)
    Set oOutput = oExec.StdOut

    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbNewLine
    Wend

    Debug.Print s

    Set oOutput = Nothing: Set oExec = Nothing
    Set oShell = Nothing

End Sub

Credit

Script borrowed from @bburns.km, non-accepted answer, from this SO post