VBA export to CSV UTF-8

Question

I'm trying to export a worksheet to CSV UTF-8.

I know that i can use the new xlCSVUTF8 option but its not supporting old Excel versions, so its not good for me.

I found a solution through ADO.stream.

This solution works well, only there is one problem, if the text I export has a comma (,) it considers it as a new column.

is there any way to defin the column seperator not to be comma (,) ?

This is the code:

Sub CreateCSVinUTF8(wS As Worksheet, FilePath As String)
Dim ByteData()  As Byte
Dim Text        As String
Dim vaInput()   As Variant
'
Dim Y As Long
Dim X As Integer


Const adCrLf                As Long = -1
Const adModeUnknown         As Long = 0
Const adSaveCreateOverWrite As Long = 2
Const adTypeText            As Long = 2
    
    
vaInput = wS.UsedRange


For Y = 1 To UBound(vaInput, 1)
    For X = 1 To UBound(vaInput, 2)
        Text = Text & vaInput(Y, X) & ","
    Next X
    Text = Left(Text, Len(Text) - 1) & vbCrLf
Next Y

With CreateObject("ADODB.Stream")
    .Mode = adModeUnknown
    .Type = adTypeText
    .LineSeparator = adCrLf
    .Charset = "UTF-8"
    .Open
    .WriteText Text
    .SaveToFile FilePath, adSaveCreateOverWrite
    .Close
End With
            
End Sub

I have now tried your solution and unfortunately it does not work. now, all the information goes into one column with few spaces. — xl0911, May 05 '21 at 13:54
Will that solve the problem? https://stackoverflow.com/questions/4617935/is-there-a-way-to-include-commas-in-csv-columns-without-breaking-the-formatting — Алексей Р, May 05 '21 at 15:03

VBA export to CSV UTF-8

0 Answers0