Sending and receiving data via POST in Microsoft W

Question

I am trying to port a macro that we have working for MS Word on Windows that uses a website to generate an equation image and returns that image for insertion into a document. The current (working on Windows) call is below. When I use the same call in OSX, I receive an error 429 stating "ActiveX component can't create object".

' Create an xmlhttp object.
Set w_page = CreateObject("Microsoft.XMLHTTP")

' Open the connection to the remote server.
w_page.Open "POST", WebAdd, False

' Indicate that the body of the request contains form data
w_page.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

' Actually send the request and return the data:
Font_Size = ComboFontSize.Value
w_page.Send "formula=" & Font_Size & "." & Latex_Str

The error is generated on the Set w_page = CreateObject("Microsoft.XMLHTTP") statement. I have tried a couple of alternate methods such as:

Set w_page = CreateObject("MSXML2.ServerXMLHTTP")

and

Set w_page = CreateObject("WinHttp.WinHttpRequest.5.1")

But the same error is generated. Please help me find the correct way of sending POSTs in OSX Word 2011.

For anybody interested, the project is available on GitHub.

Thanks a lot!

EDIT: I found this post that details an option for use with OSX Excel, but I cannot find its analog for OSX Word. Does anybody know of a Word-equivalent to the Excel ActiveSheet.QueryTables that would work for sending POSTs in OSX?

EDIT: I have made some progress. It looks like you can call external programs through VBA, and all Macs come with Python installed, so I wrote a Python script that uses urllib and urllib2 to send the request to the server. The Python file also uses argparse to parse the formula string, fontsize, and web address from the command line.

Now my macro can call my python script with something like:

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

Where Latex_Str is input by the user through the app, Font_Size is likewise defined by the user, and WebAdd is one of two addresses, depending on whether we are generating the equation or cleaning up temporary files.

What I cannot figure out is how to get VBA to read the return from my Python script (a string with return values from my server that I need in order to later retrieve the image file). The Shell command only seems return a PID number. Can anybody help?

SOLUTION: I figured it out! I was able to write a Python script to handle the POST request to the server and print its response to stdout. With the help of the community and lots of documentation online, I was then able to call this Python script using AppleScript from VBA with the result = vba.MacScript(command) method. This enabled me to read the stdout from my Python script into a string variable in VBA. My Python script was 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()

This related thread that I started more recently pointed me in the direction of the MacScript command for calling my function and getting a string return, and @CuberChase got me started down the path to writing an external function to handle the call to the server. Thanks a lot!

Answer 1

Unfortunately, there is no capacity to perform a HTTP Post request directly from VBA via Mac Office.

You are currently getting the error 429 stating "ActiveX component can't create object" because there is no XMLHTTP object model (or MSXML2 or WinHttp) in OS X, these object models are Windows only. This means they are not available to any Office programs not just Word.

You'll have to find a work around like possibly using AppleScript (not sure if it's possible or not) or issuing a shell command on an external program such as curl. This SO answer uses Curl for an HTTP Get request and is probably the best starting place.