VBA to save file as CSV UTF-8 Without BOM

Question

I use this code to save my list as a CSV UTF-8 file but it save it as a CSV UTF-8 BOM file.

I need it to be without BOM. I'm not that good at coding so hope someone will help me adjust the code, so it will save my list as a CSV UTF-8 without the BOM.

This is the code I use:

Sub SaveList_To_CSV()
    Dim MyPath As String, MyFileName As String, rng As Range
    Dim C As Range
    
    MyPath = Sheets("Dashboard").Range("B11").Value 'contain the save location for new file
    MyFileName = Sheets("Dashboard").Range("B14").Value 'contain the name for new file
    
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
    If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
    
    ActiveWorkbook.Sheets("List_Sheet").Copy 'name of the sheet we want to save as CSV
    Set rng = ActiveWorkbook.Sheets(1).UsedRange
    rng.Offset(0, 1).Clear 'keep only the first column
     
    For Each C In Selection
         If Len(C) = 0 Then C.ClearContents
    Next C
    
    ActiveWorkbook.SaveAs FileName:=MyPath & MyFileName, FileFormat:=xlCSVUTF8, CreateBackup:=False
    ActiveWorkbook.Close
      
    Workbooks.Open FileName:=MyPath & MyFileName
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    MsgBox "List Export Successful!"
End Sub

https://stackoverflow.com/questions/31435662/vba-save-a-file-with-utf-8-without-bom shows one way to remove the BOM from a UTF8 file Or see https://stackoverflow.com/questions/4143524/can-i-export-excel-data-with-utf-8-without-bom — Tim Williams, Jan 26 '22 at 20:47

CDP1802 · Answer 1 · 2022-01-27T11:24:13.167

With only one column you can create the file using a ADODB.Stream object.

ub SaveList_To_CSV()

    Dim MyPath As String, MyFileName As String
    MyPath = Sheets("Dashboard").Range("B11").Value 'contain the save location for new file
    MyFileName = Sheets("Dashboard").Range("B14").Value 'contain the name for new file
    
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
    If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
    
    'name of the sheet we want to save as CSV
    Dim rng As Range, s As String
    With ActiveWorkbook.Sheets("List_Sheet")
        Set rng = .UsedRange.Columns(1)
        If rng.Rows.Count = 1 Then
            s = rng.Value
        Else
            s = Join(Application.Transpose(rng), vbCrLf)
        End If
       
    End With
            
    Dim objStreamUTF8, objStreamUTF8NoBOm
    Set objStreamUTF8 = CreateObject("ADODB.Stream")
    Set objStreamUTF8NoBOm = CreateObject("ADODB.Stream")
    
    With objStreamUTF8
        .Charset = "UTF-8"
        .Open
        .Type = 2 'adTypeText
        .WriteText s, 1 'last line crlf
        .Position = 3
    End With

    With objStreamUTF8NoBOm
       .Type = 1 'adTypeBinary
       .Open
       objStreamUTF8.CopyTo objStreamUTF8NoBOm
       .SaveToFile MyPath & MyFileName, 2 'adSaveCreateOverWrite
    End With

    objStreamUTF8.Close
    objStreamUTF8NoBOm.Close
    
    MsgBox rng.Rows.Count & " rows Export Successful! to " & MyPath & MyFileName
End Sub

Not quite there, can see it create the file in UTF-16 instead og UTF-8. Plus I do not need to add " around the values. — Kristian, Jan 27 '22 at 06:34

VBA to save file as CSV UTF-8 Without BOM

1 Answers1