将外部脚本的结果返回到VBA [英] Returning result of an external script to VBA

问题描述

我正在将我们在Windows MS Word中工作的宏移植到OSX.该宏允许用户在Word中使用LaTeX生成方程式，并且必须向服务器发出POST请求并返回结果.在Windows中使用Microsoft.XMLHTTP对象可以正常工作，但在OSX中似乎没有等效对象.

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.

要解决此问题，我创建了一个Python脚本，该脚本使用urllib和urllib2处理请求，并允许我使用argparse发送LaTeX字符串和网址作为输入参数.该脚本执行我需要的操作，然后将Web查询的结果作为字符串返回.

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.

我在VBA中需要此字符串.

I need this string in VBA.

我当前的VBA调用类似于以下内容，其中pyPath和getURLpath是静态的，Latex_Str和Font_Size是由用户输入生成的，而WebAdd是正在运行的服务器的地址我们的服务器端脚本.

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)

问题是Shell命令仅返回您正在调用的进程的双值PID.我需要从我的Python脚本返回的实际字符串.我可以修改Python脚本以根据需要返回此脚本，但是如何将其导入VBA?

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?

有人知道如何将这个结果导入我的VBA环境吗?

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

推荐答案

从问题中提取答案:

我知道了！
我可以使用result = MacScript(command)函数调用我的Python脚本，在该函数中，我将command定义如下:

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 & """"

我的Python脚本称为getURL.py，它根据用户定义的可选参数--formula和--fontsize处理到服务器的请求，该参数分别存储在VBA中，分别为Latex_Str和Font_Size，并且服务器WebAdd的网址.我还向我的Python脚本添加了一些功能来处理传递的代理设置.上面通过MacScript传递的命令返回Python脚本的标准输出，即服务器的返回值. Python脚本如下:

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()

如果有人好奇，完整的代码将在我完成修复后的项目页面中提供.几个其他的错误并进行测试. (正在运行的)Windows版本已经在那里.

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.

这篇关于将外部脚本的结果返回到VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！