Unicode string to flat file from vba

Question

I want to store a unicode string in a flat file on a windows box from an excel/vba macro. The macro converts normal string to unicode representation, need to store it in a file and retrieve later.

Answer 1

Add a reference to "Microsoft Scripting Runtime" COM component (scrrun.dll).

It has all the classes (specifically FileSystemObject/TextStream) to create/read/write files.

Answer 2

Hi Amit,
As mentioned, you can use the Microsoft Scripting Runtime (scrrun.dll). I have posted some examples below. Some people also like the native file IO features. There is an extensive (and fairly comprehensive thread) thread here: http://www.xtremevbtalk.com/showthread.php?t=123814

However for Unicode files it's probably the least painful to use Textstreams:)

Public Sub StringToTextFile(ByVal path As String, ByVal value As String)
    'Requires reference to scrrun.dll
    Dim fso As Scripting.FileSystemObject
    Dim ts As Scripting.TextStream
    Set fso = New Scripting.FileSystemObject
    Set ts = fso.CreateTextFile(path, False, True)
    ts.Write value
    ts.Close
End Sub

Public Sub LazyMansWay(ByVal path As String, ByVal value As String)
    'Reference counting will cause the objects to be destroyed. The termination
    'events of the classes will cause the connections to be closed.
    CreateObject("Scripting.FileSystemObject").CreateTextFile(path, False, True).Write value
End Sub

Answer 3

The best solution I could figure is read the string in to a byte array and write each byte to a binary file Private Function WriteBinaryFile(ByRef szData As String) Dim bytData() As Byte Dim lCount As Long

bytData = szData
Open PwdFileName For Binary As #1
    For lCount = LBound(bytData) To UBound(bytData)
        Put #1, , bytData(lCount)
    Next lCount
Close #1

End Function

Read it back by opening the file in binary mode and reading each byte into a byte array and then converting it to a string.

Sub ReadBinaryFile(ByRef gszData As String) Dim aryBytes() As Byte Dim bytInput As Byte Dim intFileNumber Dim intFilePos

intFileNumber = FreeFile

Open PwdFileName For Binary As #intFileNumber intFilePos = 1

Do Get #intFileNumber, intFilePos, bytInput If EOF(intFileNumber) = True Then Exit Do ReDim Preserve aryBytes(intFilePos - 1) aryBytes(UBound(aryBytes)) = bytInput intFilePos = intFilePos + 1 Loop While EOF(intFileNumber) = False Close #intFileNumber

gszData = aryBytes End Sub