1. Writing Mac-compatible File I/O code in VBA

    Disclaimer: I’m not a fan of VBA. It’s a necessary evil; the reality is that Office documents are everywhere within the enterprise, and VBA is the shortest path to pull value out of documents and into other places/formats.

    I wrote up a nifty bit of VBA that applies cell values into a template to produce some basic brand-specific CSS.

    Unfortunately, the code didn’t work on our designers’ machines, as they are using Office for Mac 2011 - which supports VBA, but not all(/any?) of the “CreateObjects” like “Scripting.FileSystemObject” or “ADODB.Stream”.

    The solution is to use the old-school Open / For Input As / Line Input functions. Here’s a mapping of old to new - the old is commented out VBA-style (‘).

    You won’t need your “fso” or stream handles, but we’ll include them here for posterity:

    'Dim fso, inputStream, outputStream
    'Set fso = CreateObject("Scripting.FileSystemObject")

    You will need vars for your file paths and file handles:

    Dim inputFilePath, inputFile, outputFilePath, outputFile

    To open a file for reading, use “Open … For Input As”:

    'Set inputFile = fso.getFile(inputFilePath)
    'Set inputStream = inputFile.OpenAsTextStream(ForReading, True)
    inputFile = FreeFile
    Open inputFilePath For Input As #inputFile

    To open a file for writing, use “Open … For Output As”:

    'fso.CreateTextFile outputFileName
    'Set outputFile = fso.getFile(outputFileName)
    'Set outputStream = outputFile.OpenAsTextStream(ForWriting, True)
    outputFile = FreeFile
    Open outputFileName For Output As #outputFile

    To read some lines, you don’t need a “stream”: just read them with “Line Input”:

    Dim currentLine
    'Set inputStream = inputFile.OpenAsTextStream(ForReading, True)
    'Do While inputStream.AtEndOfStream <> True
    '    currentLine = inputStream.ReadLine
    Do While Not EOF(inputFile)
        Line Input #inputFile, currentLine

    To write some lines, again you don’t need a stream - just write them with “Line Output”:

    'outputStream.WriteLine currentLine
    Print #outputFile, currentLine

    To clean up, use the “Close” syntax:

    Close #inputFile
    Close #outputFile

    Now, the big gotcha: if you’re working with text files, odds are the input file is Unicode-encoded with a Byte Order Mark (BOM) header.

    Using these file ops to copy lines from one to the other will lead to a broken BOM header in the output, and make the file look incomprehensible (Chinese characters, in my case).

    I couldn’t find any options to get Excel to handle encodings gracefully - my “fix” was to just skip the first line of the file, which works great! If you wanted to get clever, you could try to omit the BOM from output, but why bother?

    Finally, one last trick: if you want to deal with relative file paths, you’ll probably be concatenating onto ThisWorkbook.Path . For that, you’ll need a delimiter; and it’s colon on OSX:

    Dim fsDelimiter
    fsDelimiter = "\"
    If Application.OperatingSystem Like "*Mac*" Then
        fsDelimiter = ":"
    End If

    Be careful out there.