How do I issue an HTTP GET from Excel VBA for Mac

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-macPrivate Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As LongPrivate Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As LongPrivate Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As LongPrivate Declare Function feof Lib "libc.dylib" (ByVal file As Long) As LongFunction 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 FunctionFunction 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 = sResultEnd 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!

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-macPrivate Declare PtrSafe Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As LongPtrPrivate Declare PtrSafe Function pclose Lib "libc.dylib" (ByVal file As LongPtr) As LongPrivate Declare PtrSafe Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As LongPrivate Declare PtrSafe Function feof Lib "libc.dylib" (ByVal file As LongPtr) As LongPtrFunction 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 FunctionFunction 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 = sResultEnd Function

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/shif [ -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