0

Everything works but, the code creates an txt file in UTF-8 with BOM, I would like to create a txt with UTF-8, but without BOM.

How can I do this?

Option Explicit

Sub SaveWorkSheetAsCSV()
    
    ActiveSheet.Buttons.Delete
    
    Dim FolderPath As String
    FolderPath = "C:\Users\" & Environ("USERNAME") & "\Test"
    
    Dim FileName As String: FileName = Format(Now, "yyyymmdd-hh.mm ") & " Test file"
    
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets(4)
    
    Application.ScreenUpdating = False
    
    sws.Copy
    Dim dwb As Workbook: Set dwb = ActiveWorkbook
    
    Application.DisplayAlerts = False
    dwb.SaveAs FolderPath & "\" & FileName & ".txt", xlCSVUTF8, Local:=True
    Application.DisplayAlerts = True
    dwb.Close SaveChanges:=False
    
    Application.ScreenUpdating = True
    
    ThisWorkbook.FollowHyperlink FolderPath

End Sub
Ulquiorra Schiffer
  • 89
  • 7
  • Does this answer your question? [VBA : save a file with UTF-8 without BOM](https://stackoverflow.com/questions/31435662/vba-save-a-file-with-utf-8-without-bom) – Warcupine Apr 20 '21 at 13:15
  • Atm not really, I saw that post, but I don't understand how I should integrate that part inside of my code.. – Ulquiorra Schiffer Apr 20 '21 at 13:20

1 Answers1

0

To remove the BOM, read the file into an ADODB Stream Object, move the position by 3 bytes and then using another stream save the bytes to a new file.

    dwb.Close SaveChanges:=False 
    Call RemoveBOM(FolderPath, filename) ' add this line to your code
    ThisWorkbook.FollowHyperlink FolderPath

Add this sub

Sub RemoveBOM(FolderPath As String, filename As String)

    Dim objStreamUTF8, objStreamUTF8NoBOm
    Set objStreamUTF8 = CreateObject("ADODB.Stream")
    Set objStreamUTF8NoBOm = CreateObject("ADODB.Stream")
    
    With objStreamUTF8
        .Charset = "UTF-8"
        .Open
        .LoadFromFile FolderPath & "\" & filename & ".txt"
        .Type = 2 'adTypeText
        .Position = 3
    End With

    With objStreamUTF8NoBOm
       .Type = 1 'adTypeBinary
       .Open
       objStreamUTF8.CopyTo objStreamUTF8NoBOm
       .SaveToFile FolderPath & "\" & filename & "_noBOM.txt", 2 'adSaveCreateOverWrite
    End With

    objStreamUTF8.Close
    objStreamUTF8NoBOm.Close

End Sub
CDP1802
  • 13,871
  • 2
  • 7
  • 17