Return to Word vba the result of a python script called by the vba macro in Windows

Question

I know how to use vba in MS Word in Windows (7 or 10) to run a python script. Here’s one example:

RegExpPatrn = "[RegExp search string]"
PythonScriptFullName = Chr(34) & "[PythonScriptFullname.py]" & Chr(34)
PyExe = "C:\Python27\python2.7.exe"
CmdLin = PyExe & " " & PythonScriptFullName & " " & RegExpPatrn 
Set objWshShell = CreateObject("WScript.Shell")
objWshShell.Run strCmdLin01, 1, True

But I don’t know how to return the result of the Python script (e.g., a regexp file search) to the vba macro that called the Python script.

If anyone has any suggestions, I'd appreciate getting them.

I've read about "piping", but I don't understand how to do that.

I've read about using environment variables, but I haven't been able to use python to create an environment variable that I was able to retrieve after the python script had run.

If anyone has any suggestions, I'd appreciate getting them.

Marc

. .

**

SOLUTION added November 10, 2016, thanks to user235218 and Tim Williams:

** The following are:

1. My python script;

2. My version of your vba sub; and

3. My version of your vba function.

Here's My python script:

print("This is my Python script's output")

Here's My version of your vba sub:

    Sub subTestfnShellOutput()
    Dim strPythonPath As String
    Dim strPyScript As String
    Dim strCmd As String

    strPythonPath = "C:\Python27\python2.7.exe"
    strPyScript = "C:\Apps\UtilitiesByMarc\Test_2016-11-09_StackOverFlowSoution_aaa_.py"
    strCmd = strPythonPath & " " & strPyScript

    'Debug.Print fnShellOutput("python D:\temp\test.py") ''>> hello world
    MsgBox fnShellOutput(strCmd) ''>> hello world        
    End Sub 'subTestfnShellOutput

Here's My version of your vba function:

    Function fnShellOutput(cmd As String) As String
    ' https://stackoverflow.com/questions/39516875/return-result-from-python-to-vba

    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")
    Set oExec = oShell.Exec(cmd)
    Set oOutput = oExec.StdOut

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

    """November 10, 2016 Addendum:  It works now that I replaced 'ShellOutput = s' with
     'fnShellOutput = s'"""
    fnShellOutput = s

    Set oOutput = Nothing
    Set oExec = Nothing
    Set oShell = Nothing    
    End Function 'fnShellOutput

Answer 1

If you use print() in your Python script then the printed content will be sent to stdout.

Using a WScript.Shell object to run your script gives you access to the stdout content.

Here's a very basic example:

Test python script test.py:

print("hello world")

VBA:

Sub TestIt()
    Debug.Print ShellOutput("python D:\temp\test.py") ''>> hello world
End Sub

Function ShellOutput(cmd As String) As String

    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")
    Set oExec = oShell.Exec(cmd)
    Set oOutput = oExec.StdOut

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

    ShellOutput = s

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

End Function

Adapted from: Return result from Python to Vba