Extracting unicode text from Excel spreadsheet using VBA

Question

We generate HTML from text in an Excel spreadsheet. The text contains unicode representations of international characters. When we use VBA to extract the text and output it to a file, it is written as ANSI (ASCII). Is there a way to preserve the unicode representation using VBA?

Bruce

Answer 1

The default file writing mechanisms in VBA are ANSI (just like VB6).

You need to use a different method. One way is to use the FileSystemObject.

   Dim fso As Object, MyFile As Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set MyFile = fso.CreateTextFile("c:\testfile.txt", False,True) 'Unicode=True'
   MyFile.WriteLine("This is a test.")
   MyFile.Close