6

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?

Andrew LaPrise
  • 3,373
  • 4
  • 32
  • 50
Anas K
  • 741
  • 2
  • 7
  • 15

1 Answers1

15

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

Community
  • 1
  • 1
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • This works, but it is extremely slow when there are many lines of output to iterate over. Do you know why it is so slow? – joejoejoejoe4 Aug 22 '21 at 13:30
  • @jippyjoe4, all depends on your use case and what you mean by *extremely slow*. Please ask a new question if you need help. – Parfait Aug 22 '21 at 15:19
  • 1
    Nice! This works in VB6 as well when many other solutions would not work. I had to first add "Windows Script Host Object Model" under Project -> References. – Doctuh D. Nov 18 '22 at 17:04