As much as Excel 2011 for the Mac is cool (with VBA and full Windows compatibility - at least to my experience so far!), one can always find a glitch. Tuesday, 10 January 2012 at 05:42:00 GMT+9 Anonymous said. You can access the VBA environment in Excel 2011 for Mac by opening the Visual Basic editor. First, be sure that the Developer tab is visible in the toolbar in Excel. The Developer tab is the toolbar that has the buttons to open the VBA editor and create Form Controls like buttons, checkboxes, etc.
So, I have this cool social media news research job I do in the mornings for a client. It involves collecting a lot of buzz from various corners of the web, putting them into an Excel file, and having to spit out a report in the form of a text file. The formatting has to be a certain way so I put together a VBA script (macro) to do this.
I grab soundbites from from all over the world, so needless to say the text file I output needs to be in UTF-8 format. Incredibly, this doesn't come naturally to Excel.
In VBA on Windows Excel, there was the ADODB.Stream class from which we can instantiate a file object which allows us to set the encoding using the Charset property, as I do in the example VBA snippet below.
Set fs = CreateObject('ADODB.Stream')
fs.Type = 2
fs.Charset = 'utf-8'
' Loop thru your cells
Excel For Mac Vba 2011 Version
fs.writetext myCell & Chr(10)
fs.SaveToFile 'OutputSheet.txt', 2
Anywhoo, after moving to the Mac I realized to my dismay that ADODB.Stream is not available. Which makes sense, as it is an Active X.
Excel For Mac Vba 2011 ProductI spent weeks Googling around for a VBA solution to this problem which, for the hell of it, I'll restate: The ability to output multilingual text from worksheet cells in UTF-8 format to a text file, in Excel on the Mac. But to no avail. Am I really the only person in the world struggling with this? Or do I just suck as a Googler? Could it be the martinis?
To make a long story short, I took the bold step of ditching VBA and adopted AppleScript as my language of choice on MS Office for the Mac. When in Rome... and all that.
I have attached an AppleScript file, and a corresponding Excel test file to illustrate. To test it out you will need to open the Excel file, open the AppleScript file with the AppleScript Editor, and hit Run from the menu (or Cmd-R).
Here is the entire AppleScript.
tell application 'Microsoft Excel'
set outFile to (path of active workbook)
set outFile to (outFile & ':OutputUTF8.txt')
set openFile to open for access file outFile with write permission
set eof openFile to 0
set title to (name of active workbook) & return
write title to openFile as «class utf8»
set rowNum to 1
set rowNum to (rowNum + 1)
set cellVal to (value of cell rowNum of column 1 of active sheet)
if (cellVal = ') then
set langStr to (value of cell rowNum of column 1 of active sheet)
set textStr to (value of cell rowNum of column 2 of active sheet)
set outStr to langStr & ':' & textStr & return
write outStr to openFile as «class utf8»
close access openFile
Here is the salient line of code that allows me to specify the encoding. It doesn't seem to be at the file level but at the level of each write statement.
write outStr to openFile as «class utf8»
Download the AppleScript:
Download the Excel file:
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:
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):
' execShell() function courtesy of Robert Knight via StackOverflow
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
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
exitCode = pclose(file)
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
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:
This is what we wanted! We are done!