Removing CrLf in whole csv in VBA - ADODB.Stream (Excel Macro)

Question

I have a vba macro with that adds BOM to UTF-8 csv - it's needed for succesfully opening in Excel. But the problem is, that when at the end of the line there is CrLf mark - excel makes new line below that line. What I need is to remove all CrLf marks (no Cr or Lf should be added instead). It should help because only Cr will exist in csv. CrLf exists only in fault lines.

Can you help please with my source code? What formula should I add to replace in source csv to save target csv with BOM without any CrLf?

Dim fsT, tFileToOpen, tFileToSave As String

tFileToOpen = "C:\source_NO_BOM.csv"
tFileToSave = "C:\target_WITH_BOM.csv"

tFileToOpenPath = tFileToOpen
tFileToSavePath = tFileToSave

Set fsT = CreateObject("ADODB.Stream"): 'Create Stream object
fsT.Type = 2: 'Specify stream type – we want To save text/string data.
fsT.Charset = "utf-8": 'Specify charset For the source text data.

fsT.Open: 'Open the stream
fsT.LoadFromFile tFileToOpenPath: 'And write the file to the object stream
fsT.SaveToFile tFileToSavePath, 2: 'Save the data to the named path

Answer 1

You can create another stream object, change the content you broke into it, and export it.

Dim fsT, tFileToOpen, tFileToSave As String
Dim s As String, Newfst As Object


tFileToOpen = "C:\source_NO_BOM.csv"
tFileToSave = "C:\target_WITH_BOM.csv"


tFileToOpenPath = tFileToOpen
tFileToSavePath = tFileToSave


Set fsT = CreateObject("ADODB.Stream"): 'Create Stream object

With fsT
    .Type = 2: 'Specify stream type ? we want To save text/string data.
    .Charset = "utf-8": 'Specify charset For the source text data.
    
    .Open: 'Open the stream
    .LoadFromFile tFileToOpenPath: 'And write the file to the object stream
    s = .ReadText
    s = Replace(s, vbCrLf, "")
End With

Set Newfst = CreateObject("ADODB.Stream")
With Newfst
    .Type = 2
    .Charset = "utf-8"
    .Open
    .WriteText s
    .SaveToFile tFileToSave, 2
End With