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:

My python script;
My version of your vba sub; and
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

Probably the easiest implementation would be to modify the python script to write a text file in a known location, and have the VBA then query that file. But that becomes difficult to implement in a distributed environment. The proper way to do this would be to register your python program as a COM server with accessible methods (basically an API) and then your VBA can just create an instance of that object and invoke it's methods, which return directly to the caller (VBA). — David Zemens, Nov 09 '16 at 05:12
Thank you. While doing some research to learn about what it means to register a python program as a COM server, I came across Pyro4, which ostensibly is a library that enables you to build applications in which objects can talk to each other over the network, with minimal programming effort. May I follow up with you later, if I can't figure out where to see an example showing how to register a python program as a COM server, and then use it as you indicated? — Marc B. Hankin, Nov 10 '16 at 05:14
Your function is called `fnShellOutput` but you set the return value using `ShellOutput ` — Tim Williams, Nov 10 '16 at 05:32
If that change doesn't fix it then you need to explain exactly what happens when you run your version. — Tim Williams, Nov 10 '16 at 05:36
Do you know of a URL where I could learn about what you showed me, i.e., creating standard output, and harvesting it, using python and using vba? please don't spend more than a moment or two on this because you've spent a lot of time helping me, and I don't want to abuse your kindness. Thank you so much. — Marc B. Hankin, Nov 10 '16 at 15:17
Sorry I don't really use Python at all, I just remembered that approach from a previous question and was able to find an example. — Tim Williams, Nov 10 '16 at 15:49

Tim Williams · Accepted Answer · 2016-11-11T00:49:28.507

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

Please take a peek at my addendum that I added today, responding to your posting. And thank you for your efforts. They are much appreciated. Marc — Marc B. Hankin, Nov 10 '16 at 05:27

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

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

1 Answers1