Excel on Mac Get Web Content (Get HTTP)

I made the switch to Mac and now some of my old Excel macros that are depended on the operating system do not work. One particular item is the ability to fetch data from the Web. So here I am after many years using Office and Excel learning again. This is fun!

Ok, let’s get series now…Today’s goal is to build a user defined function that can be called from the Excel datasheet and fetch a web page. We will call that function getHTTP and it will work on Mac (but not on Windows).

Luck is on my side as there are many others looking for the same solution. I found an entry called “How do I issue an HTTP GET from Excel VBA for Mac 2011” (See it here) and it has exactly what we need.

Navigation in Excel on Mac is just a little bit different so here are steps by step instructions:

1. Open an Excel file or create a new one.

2. Save as  “Excel Macro-Enabled Workbook (.xlsm)” file.

3. From the menu navigate to Tools->Macro->Macros…

4. In the “Macro Name:” field type the name of the new Macro we want to create. In this case getHTTP. You should see a screen like this:

Excel on Mac Create Macro

 

5. Click “Create” and you will find yourself in the VBA editor with an empty Sub called getHTTP.

6. Next we will copy paste the VBA code from below (taken from the link at stackoverflow listed above with minor change of the function name from HTTPGet to getHTTP just cause I like this format better):


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 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 getHTTP(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
getHTTP = sResult
End Function

7. And we are ready to give it a try… Leave the VBA editor and return to the Excel sheet.

8. Let’s put in cell A2 the value: http://api.openweathermap.org/data/2.1/forecast/city

9. Let’s pub in cell B2 the value: q=Boston

10. Let’s pub in cell C2 the value: =getHTTP(A2,B2)

11. The page should refresh and you should see a lot of gibberish JSON things. Something like this:

Excel on Mac Get Web Content (Get HTTP)

This is what we wanted! We are done!

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

5 thoughts on “Excel on Mac Get Web Content (Get HTTP)”

  1. Dave, I get errors. When I run this, I get an error:”only comments may appear after end sub/end function or end property. Please advise. I pasted the code in the B editor, to out the extra “end” function at the bottom.
    When the error message pops up, the VB editor highlights the line “Private Declare Function popen Lib “libc.dylib” (ByVal command As String, ByVal mode As String) As Long”

    1. Hi Stephen,
      Just tried it again and it seems to work just fine on my Mac. I did saw few formatting issues in the initial blog entry and that may have been the issue. Even in the new format i had to manually delete some extra empty lines after i pasted the code in the VB editor.

      Can you retry it again and let me know what you get?

      1. Dave, sorry- no dice. I get the same results. I am using an iMAC, Office 2011. I have never used VB before in XL, just functions, so am newbie in this. Is there a special mode I need to be in or something to make the macro execute properly?

  2. Ok, did a review again with the thought of “let’s follow the instructions one by one and see what can go wrong”. I really should have done that from the beginning…
    I think I got it!

    When you follow step 5 and create the new macro, VB will create an empty function for you. Something like this:
    Sub getHTTP()

    End Sub

    You have to delete these lines and make sure that the edit area is completely empty before you paste the content.
    Once you do that the Macro should work.

    Please try and let me know if it works so i can update the post with better instructions…

    One additional discovery. The specific example of how to use getHTTP uses weather API. While the example still return positive response and some content, it is not returning what you ask for.

    Let me explain this further:
    The original example uses version 2.1 of the API. Since that time openweathermap.org released version 2.5 of the API. The new API requires registration and specific API key (annoying).
    You can still use version 2.1 of the API but it will ignore the q=Boston and instead return a large response with multiple cities and not what you searched for.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s