在VBA - ADODB中删除整个csv中的CrLf.流(Excel宏)

Question

我有一个vba宏，将BOM添加到UTF-8 csv -这是在Excel中成功打开所需要的。但问题是，当在一行的末尾有CrLf标记时，excel会在该行下面创建新行。我需要的是删除所有CrLf标记(不应该添加Cr或Lf)。它应该有帮助，因为csv中只有Cr存在。crf只存在于断层中。

你能帮助我的源代码吗?我应该添加什么公式来替换源csv以保存目标csv与BOM没有任何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

您可以创建另一个流对象，更改您插入其中的内容，并导出它。

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