Return result from Python to Vba

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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:PathToPythonScript.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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0