Return result from Python to Vba

by

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

More Related Contents:

  1. How to call python script on excel vba?
  2. Running an Excel macro via Python?
  3. Calling python script from excel/vba
  4. Write pandas dataframe to xlsm file (Excel with Macros enabled)
  5. Use Python to Inject Macros into Spreadsheets
  6. How do I call an Excel macro from Python using xlwings?
  7. Excel, How to split cells by comma delimiter into new cells
  8. Read streaming input from subprocess.communicate()
  9. Add missing dates to pandas dataframe
  10. Pandas: sum DataFrame rows for given columns
  11. Multiple variables in a ‘with’ statement?
  12. How to len(generator()) [duplicate]
  13. Difference between python3 and python3m executables
  14. Rename MultiIndex columns in Pandas
  15. Cross-platform space remaining on volume using python
  16. Python Pandas update a dataframe value from another dataframe
  17. Apply fuzzy matching across a dataframe column and save results in a new column
  18. python dataframe pandas drop column using int
  19. How to aggregate unique count with pandas pivot_table
  20. Ampersand “&” syntax error running any Python script in VSCode?
  21. How to read lines from a file in python starting from the end
  22. How do I connect to SQL Server via sqlalchemy using Windows Authentication?
  23. Install Shapely: OSError: [WinError 126] The specified module could not be found
  24. Cartesian Product in Tensorflow
  25. Django-DB-Migrations: cannot ALTER TABLE because it has pending trigger events
  26. Module imports and __init__.py
  27. How to set connection timeout in SQLAlchemy
  28. Browser performance tests through selenium
  29. Shift rows of a numpy array independently
  30. How to change the foreground or background colour of a selected cell in tkinter treeview?

Leave a Comment