Return Result from Python to Vba

Return result from Python to Vba

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

Calling Python from VBA - returning Python function value

I couldn't find any direct way to transfer value returned by Python function, so I handled it by saving Python result to text file on Desktop and reading + deleting the same file with VBA.

Important things are:

1. shellObj.Run unlike Shell allows to set a Boolean parameter to wait for return to True, so that the rest of VBA code doesn't try to get data from text file while there isn't any created yet.

2. If text file doesn't have a full file path specified, only a file name, Python won't create text file in directory in which Python script is located, as it happens when I run script through command line or Visual Studio. Apparently when called from VBA, Python has a different perception of default directory and it isn't a directory of VBA file either.

Here is my Python code:

def myFunction():
    # do stuff
    return stuff_output

import os

f = open(os.path.expanduser("~\Desktop") + "\myTemp.txt","w")   #create a new text file on Desktop in write mode
f.write(str(myFunction()))  #write output to created text file 
f.close()   #close file object

Here is my VBA code:

Option Explicit

Sub RunPython()

    Dim shellObj As Object: Set shellObj = CreateObject("WScript.Shell")
    Dim tempTxtPath As String

    Const scriptPath As String = "C:\Users\Rychu\Desktop\python\myPythonScript.py"
    Const pythonPath As String = "C:\Python36-32\python.exe"
    tempTxtPath = CreateObject("WScript.Shell").specialfolders("Desktop") & "\myTemp.txt"

    shellObj.Run pythonPath & " " & scriptPath, vbHide, True

    Debug.Print (getTextAndDelete(tempTxtPath))

End Sub

Function getTextAndDelete(filePath) As String

    Dim fileNum As Long
    Dim myData As String

    fileNum = FreeFile
    Open filePath For Binary As #fileNum

    myData = Space$(LOF(1))
    Get #fileNum, , myData
    Close #fileNum

    Kill filePath

    getTextAndDelete = myData

End Function

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

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

Returning result of an external script to VBA

Answer extracted from question:

I got it working!

I was able to call my Python script using the result = MacScript(command) function where I defined my command as the following:

command = "do shell script """ & pyPath & "python " & getURLpath & "getURL.py --formula '" _
          & Latex_Str & "' --fontsize " & Font_Size & " " & WebAdd & """"

My Python script is called getURL.py, and handles the request to the server based on the optional arguments --formula and --fontsize defined by the user and stored in VBA as Latex_Str and Font_Size respectively, and the web address of the server WebAdd. I also added some functionality to my Python script to handle passing proxy settings. The above command passed through MacScript returns the stdout of the Python script, which is the return from the server. The Python script is as follows:

# Import the required libraries
from urllib import urlencode
from urllib2 import Request, urlopen, URLError, ProxyHandler, build_opener, install_opener
import argparse

# Set up our argument parser
parser = argparse.ArgumentParser(description='Sends LaTeX string to web server and returns meta data used by LaTeX in Word project')
parser.add_argument('webAddr', type=str, help='Web address of LaTeX in Word server')
parser.add_argument('--formula', metavar='FRML', type=str, help='A LaTeX formula string')
parser.add_argument('--fontsize', metavar='SIZE', type=int, default=10, help='Integer representing font size (can be 10, 11, or 12. Default 10)')
parser.add_argument('--proxServ', metavar='SERV', type=str, help='Web address of proxy server, i.e. http://proxy.server.com:80')
parser.add_argument('--proxType', metavar='TYPE', type=str, default='http', help='Type of proxy server, i.e. http')

# Get the arguments from the parser
args = parser.parse_args()

# Define formula string if input
if args.formula:
    values = {'formula': str(args.fontsize) + '.' + args.formula}   # generate formula from args
else:
    values = {}

# Define proxy settings if proxy server is input.
if args.proxServ:       # set up the proxy server support
    proxySupport = ProxyHandler({args.proxType: args.proxServ})
    opener = build_opener(proxySupport)
    install_opener(opener)

# Set up the data object
data = urlencode(values)
data = data.encode('utf-8')

# Send request to the server and receive response, with error handling!
try:
    req = Request(args.webAddr, data)

    # Read the response and print to a file
    response = urlopen(req)
    print response.read()

except URLError, e:
    if hasattr(e, 'reason'):    # URL error case
        # a tuple containing error code and text error message
        print 'Error: Failed to reach a server.'
        print 'Reason: ', e.reason
    elif hasattr(e, 'code'):    # HTTP error case
        # HTTP error code, see section 10 of RFC 2616 for details
        print 'Error: The server could not fulfill the request.'
        print 'Error code: ', e.code
        # print e.read()

If anyone is curious, the full code will be available on the project page once I am done fixing a couple additional bugs and testing it. The (working) Windows version is already up there.

---

Related Topics