Sunday, May 22, 2011

As if I'm not discussing enough different unrelated technologies in this blog, today's topic is Microsoft's IIS.

In the past couple of months I suddenly found myself having to write Classic ASP. I had done such a project in the past, using IIS 5. However this was IIS 7, and my old sample scripts were not working.

Sunday, May 08, 2011

Among my myriad headaches in switching back to the Mac as my primary machine, here is another doozy.

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.

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.

I 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" activate 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 repeat set rowNum to (rowNum + 1) set cellVal to (value of cell rowNum of column 1 of active sheet) if (cellVal = "") then exit repeat else 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» end if end repeat close access openFile end tell

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»