How do I issue an HTTP GET from Excel VBA for 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?

回答1:

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    

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

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

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

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

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

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

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.

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

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!

回答2:

The answer above from John Stephens is fantastic (please upvote it!), but it no longer worked for me in the more recent Excel:mac 2016, with an error that the code needs to be updated for use on 64-bit systems.

Taking some tips from an issue I found in a related repository, I was able to adjust the data types in John's script to work correctly in Excel:mac 2016:

Option Explicit

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

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

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    Dim file As LongPtr
    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

回答3:

Another option (update accordingly if your curl is not located in /opt/local/bin/curl):

VBA:

Public Function getUrlContents(url) As String
    Dim command As String
    command = "do shell script ""/path_to/getUrl.sh " + url + """"
    getUrlContents = VBA.MacScript(command)
End Function

/path_to/getUrl.sh:

#!/bin/sh

if [ -z "$1" ]
  then
    echo "missing url argument"
else
    /opt/local/bin/curl "$1"
fi

Note that you will have to ensure that getUrl.sh is executable:

chmod u+x getUrl.sh