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:
10. Let’s pub in cell B2 the value:
11. Let’s pub in cell C2 the value:
12. The page should refresh and you should see a lot of gibberish JSON things. Something like this:
This is what we wanted! We are done!