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:
'inputStream.Close 'outputStream.Close 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.