How do I export csv with unicode by VBA

[JoyShih]

Hi
I tried to find as many as reference on the web, to find out how to export unicode csv.
But unfortunatly I still got wrong data when original file with Japanese words.
How shall I do? Many thanks.

My current code is something like

Dim arr1() As Variant
Dim Delimiter As String
Dim Filename As String
Dim R As Long
Dim Rng As Range
Dim Text As String
Dim Wks As Worksheet
Dim C As Long

Delimiter = StrConv(",", vbUnicode)
Set Wks = Worksheets(1)
Set Rng = Wks.UsedRange

Filename = ActiveWorkbook.Path & "\" & Wks.Name & ".csv"

ReDim arr1(1 To Rng.Rows.Count)

Open Filename For Output As #1
For R = 1 To Rng.Rows.Count
arr1 = WorksheetFunction.Index(Rng.Rows(R).Cells.Value, 1, 0)
Text = Join(arr1, Delimiter)
'Text = StrConv(Text, vbUnicode)
Print #1, Text

Next R
Close #1
MsgBox "OK"

 

[pbornemeier]

Would it help if you saved the entire workbook in Unicode format:
ActiveWorkbook.SaveAs Filename:="whatever.txt", FileFormat:=xlUnicodeText

This link gives some good info about SaveAs in Excel 2007 and later:
http://www.rondebruin.nl/saveas.htm

 

[Rajeev_Masand]

Hi for the same above code how can i get the file generated to be unicode ... i mean the code should be able to write the existing file to a unicode text file

 

[pbornemeier]

I believe the code in post #2 should save any worksheet in unicode format.

This site has a lot of information about Excel "Save As" method.

This site talks about using google spreadsheet for unicode conversion.