How to read unicode and write out to text file, with macro?

[ericblair]

I have some worksheets that contains chinese characters (unicode I presume.) I am looping through sheets and writing the stuff out to a text file, however, when I open the text file the chinese chars are converted to "????"

This is the code I use to read in the chars from excel:

Do While CellColCount <= ColCount
 StringStore = StringStore + " '" + CStr(Current.Cells(Row, CellColCount + 1)) + "'"
 If CellColCount <> ColCount Then
 StringStore = StringStore + ", "
 End If
 CellColCount = CellColCount + 1
 Loop

And here I write it out:

Print #fHandle, StringStore

My question: How do I read the chinese chars and qrite them out to the text file also in VBA?

Thanks!!

 

[pgc01]

Hi

Maybe this can get you started.

I compose a string with the values in A1:A3 and write it into a text file that supports Unicode.

1 - in the vbeditor set the reference Tools->References->Microsoft Scripting Runtime

2 - Write some values in A1:A3, using some of your chinese characters, and try:

Sub Test()
Dim s As String
Dim fs As FileSystemObject
Dim txtf As TextStream

' write the text of A1:A3 separated with commas into s
s = Join(Application.Transpose(Range("A1:A3")), ",")

' write the string into a text file
Set fs = New FileSystemObject
Set txtf = fs.CreateTextFile(Filename:="c:\tmp\excel\test.txt", Overwrite:=True, Unicode:=True)
txtf.WriteLine s
txtf.Close
End Sub

Remark: don't forget to change the pathname of the output file, as you may not have the folder I use.

 

[ericblair]

Perfect, Thanks!!

 

[pgc01]

You're welcome. Thanks for the feedback.