如何从 Excel VBA for Mac 发出 HTTP GET [英] How do I issue an HTTP GET from Excel VBA for Mac

问题描述

我需要从 Excel for Mac 2011 向 Web 服务发出带有查询字符串的 HTTP Get.我已经看到了使用 QueryTables 的答案(如何使用 VBA 从 Excel 向服务器发送 HTTP POST 请求?) 但他们使用 POST 方法，而不是 GET 方法.我也看到在 Windows 机器上很容易，但我被困在 Mac 上.

I need to issue an HTTP Get with a query string to a web service from Excel for Mac 2011. I've seen the answers for using QueryTables (How can I send an HTTP POST request to a server from Excel using VBA?) but they use the POST method, not a GET method. I also see that it's easy from a Windows machine, but I'm stuck on a Mac.

有什么建议，还是没有希望?

Any suggestions, or is it hopeless?

推荐答案

做进一步的研究，我看到了 Robert Knight 对这个问题的评论 Office 2011 for Mac 中的 VBA Shell 函数 并使用他的 execShell 函数构建了一个 HTTPGet 函数来调用 curl.我已经在运行 Mac OS X 10.8.3 (Mountain Lion) 和 Excel for Mac 2011 的 Mac 上对此进行了测试.这是 VBA 代码:

Doing further research, I came across Robert Knight's comment on this question VBA Shell function in Office 2011 for Mac and built an HTTPGet function using his execShell function to call curl. I've tested this on a Mac running Mac OS X 10.8.3 (Mountain Lion) with Excel for Mac 2011. Here is the VBA code:

Option Explicit

' execShell() function courtesy of Robert Knight via StackOverflow
' https://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac

Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    Dim file As Long
    file = popen(command, "r")

    If file = 0 Then
        Exit Function
    End If

    While feof(file) = 0
        Dim chunk As String
        Dim read As Long
        chunk = Space(50)
        read = fread(chunk, 1, Len(chunk) - 1, file)
        If read > 0 Then
            chunk = Left$(chunk, read)
            execShell = execShell & chunk
        End If
    Wend

    exitCode = pclose(file)
End Function

Function HTTPGet(sUrl As String, sQuery As String) As String

    Dim sCmd As String
    Dim sResult As String
    Dim lExitCode As Long

    sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
    sResult = execShell(sCmd, lExitCode)

    ' ToDo check lExitCode

    HTTPGet = sResult

End Function    

要使用它，请复制上面的代码，在 Excel for Mac 2011 中打开 VBA 编辑器.如果您没有模块，请单击插入"->模块".将代码粘贴到模块文件中.离开 VBA 编辑器 (clover-Q).

To use this, copy the code above, open the VBA editor in Excel for Mac 2011. If you don't have a module, click Insert->Module. Paste the code into the module file. Leave the VBA editor (clover-Q).

这是一个使用天气预报 Web 服务的具体示例 (http://openweathermap.org/wiki/API/JSON_API)

Here's a specific example using a weather forecast web service (http://openweathermap.org/wiki/API/JSON_API)

单元格 A1 将保留用于城市名称.

Cell A1 will be reserved for the name of the city.

在单元格 A2 中，输入 URL 字符串:http://api.openweathermap.org/data/2.1/forecast/city

In cell A2, enter the URL string: http://api.openweathermap.org/data/2.1/forecast/city

在将构建查询字符串的单元格 A3 中，输入:="q=" &A1

In cell A3 which will build the query string, enter: ="q=" & A1

在单元格 A4 中，输入:=HTTPGet(A2, A3)

In cell A4, enter: =HTTPGet(A2, A3)

现在，在单元格 A1 中输入城市名称，例如 London，单元格 A4 将显示包含伦敦天气预报的 JSON 响应.将 A1 中的值从 London 更改为 Moscow -- A4 将更改为 JSON 格式的莫斯科预测.

Now, type a city name in cell A1, for example London, cell A4 will show you the JSON response containing the weather forecast for London. Change the value in A1 from London to Moscow -- A4 will change to the JSON-formatted forecast for Moscow.

显然，使用 VBA，您可以解析和重新格式化 JSON 数据并将其放置在工作表中需要的位置.

Obviously, using VBA, you could parse and reformat the JSON data and place it where needed in your worksheet.

没有对性能或可扩展性的要求，但对于从 Excel for Mac 2011 对 Web 服务的简单一次性访问，这似乎可以解决问题并满足我发布原始问题的需求.天啊！

No claims for performance or scalability, but for a simple one-shot access to a web service from Excel for Mac 2011, this seems to do the trick and met the need for which I posted my original question. YMMV!

这篇关于如何从 Excel VBA for Mac 发出 HTTP GET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！