user235218 user235218 - 26 days ago 4
Python Question

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

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

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