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

问题描述

我需要通过Mac 2011的Excel向Web服务发布带有查询字符串的HTTP Get.我已经看到了使用QueryTables的答案(

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)对这个问题的评论

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服务进行简单的一次性访问，这似乎可以解决问题，并且满足了我提出原始问题的需要. YMMV ！

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!

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