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
– David Zemens, Commented Nov 9, 2016 at 5: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
– Marc B. Hankin, Commented Nov 10, 2016 at 5:14
Your function is called fnShellOutput but you set the return value using ShellOutput — Tim Williams
– Tim Williams, Commented Nov 10, 2016 at 5:32
If that change doesn't fix it then you need to explain exactly what happens when you run your version. — Tim Williams
– Tim Williams, Commented Nov 10, 2016 at 5:36

Tim Williams · Accepted Answer · 2016-11-11 00:49:28Z

4

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

edited Nov 11, 2016 at 0:49

answered Nov 9, 2016 at 6:34

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marc B. Hankin Over a year ago

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

Collectives™ on Stack Overflow

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 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related