Returning result of an external script to VBA

Question

I am working on porting a macro that we have working in MS Word for Windows to OSX. The macro allows the user to generate equations using LaTeX in Word, and has to make a POST request to a server and return the result. This works fine in Windows with the Microsoft.XMLHTTP object, but there does not appear to be an equivalent in OSX.

To get around this, I have created a Python script that uses urllib and urllib2 to handle the request, and allows me to send my LaTeX string and web address as input arguments using argparse. This script does what I need it to, and returns the result from the web query as a string.

I need this string in VBA.

My current VBA call is something like the following, where pyPath and getURLpath are static, Latex_Str and Font_Size are generated by user input, and WebAdd is the address of the server that is running our server-side script.

sCmd = """" & pyPath & "python"" """ & getURLpath & "getURL.py"" --formula """ & _
       Latex_Str & """ --fontsize " & Font_Size & " """ & WebAdd & """"
sResult = Shell(sCmd, vbNormalFocus)

The problem is that the Shell command only returns the double-valued PID of the process you are calling. I need the actual string returned from my Python script. I can modify the Python script to return this however I want, but how do I get it in to VBA?

Does anybody know how I can get this result into my VBA environment?

Answer 1

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.