VBA Export to CSV UTF-8 - without comma delimiter

[nd0911]

Hello,

I have a code that export data to a CSV UTF-8 file (using ADO.stream library).

The code works well but there is one problem, if the text I export has a comma (,) it considers it as a new column.

How can I perform this task without the CSV file thinking that the commas I have in the text are supposed to break the text into different columns ?

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

 

[Marc L]

Hello,​

​

as CSV means 'comma separated values' if your data have any comma in any column you must export those columns delimited by double quotes​

​

like for example this 3 columns row 1,"Hell, Marc",2021/5/5 …​

 

[nd0911]

it works, thank you