I had to export excel file to csv file through vba script.
csv file must be utf-8 code and delimted is "|"
I use SetLocaleInfo to set list separate as "|", but it only effect "xlcsv" not effect "xlunicodetext".
Private Function SetLocalSetting(LC_CONST As Long, strSetting As String) As Boolean
SetLocaleInfo GetUserDefaultLCID(), LC_CONST, strSetting
End Function
SetLocalSetting LOCALE_SLIST, "|"
When to use "xlUnicodeText", code is UTF-8 ,but the list seperator still is "Tab"
.SaveAs filename:="C:\temp\1.csv", FileFormat:=xlUnicodeText, Local:=True
When to use "xlCSV", the list seperator still is "|" what I need, but codepage is ANSI.
.SaveAs filename:="C:\temp\1.csv", FileFormat:=xlcsv, Local:=True
How to export a csv file with "|" seperator and UTF-8 ?
解决方案
Sub saveUnicodeCSV()
Set oAdoS = CreateObject("ADODB.Stream")
oAdoS.Charset = "UTF-8"
oAdoS.Mode = 3
oAdoS.Type = 2
oAdoS.Open
lRow = 1
lCol = 1
Do Until Sheets(1).Cells(lRow, lCol).Value = ""
oAdoS.WriteText (Sheets(1).Cells(lRow, lCol).Text)
lCol = lCol + 1
Do Until Sheets(1).Cells(lRow, lCol).Value = ""
oAdoS.WriteText ("|" & Sheets(1).Cells(lRow, lCol).Text)
lCol = lCol + 1
Loop
oAdoS.WriteText (vbCrLf)
lCol = 1
lRow = lRow + 1
Loop
oAdoS.SaveToFile "test.csv", 2
oAdoS.Close
Set oAdoS = Nothing
End Sub
Greetings
Axel