Excel on Mac Get Web Content (my WebService)


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…

In the Windows version of Excel there is a function call WEBSERVICE that will execute a get HTTP request and return meaningful result. But that function is not available in the Mac version of Excel.

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 myWebService and it will work on Mac (but not on Windows).

Luck is on our 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” and we will use that code as a baseline.

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 and select “Visual Basic Editor”

4. An empty VBA project will be opened.

5. Double-click on the “Module1” line and you will find yourself in an empty VBA editor window.

6. Next copy/paste the VBA code from below:

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

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

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

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

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

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

mac-get-HTTP-1

This is what we wanted! We are done!


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

  • 1
    SutoCom on April 9, 2014 Reply

    Reblogged this on Sutoprise Avenue, A SutoCom Source.

  • 2
    Stephen M Dye on May 26, 2016 Reply

    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”

    • 3
      officewizkid on May 27, 2016 Reply

      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?

      • 4
        Stephen M Dye on May 28, 2016 Reply

        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?

  • 5
    officewizkid on May 29, 2016 Reply

    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

Your email address will not be published. Required fields are marked *