Saving a text file from VBA without Byte Order Marker (BOM) and without using ADODB.Stream-CodePudding

I need to save a text file in Excel VBA without the Byte Order Marker (BOM). I am aware of different solutions to this problem, such as the one given here Can I export excel data with UTF-8 without BOM?

However, the file I need to save is rather large and it seems to be significantly faster to save it using the following code:

Set fso = CreateObject("Scripting.FileSystemObject")
Set oFile = fso.CreateTextFile("File.txt")
oFile.WriteLine StringToSaveInFile
oFile.Close

instead of using adodb.stream, as done in the example in the link. Unfortunately, using the code above includes the BOM, does anyone know of a method in VBA for removing the BOM without using adodb.stream?

CodePudding user response：

There is a lower level read/write operation available in VBA: Get/Put

    Dim strFile as String
    Dim nFileNum As Integer
    Dim byteData() As Byte

    nFileNum = FreeFile

    strFile = "c:\myfile.txt"
    Open strFile For Binary Access Write As nFileNum
    byteData = StrConv("this string is treated as a raw byte array", vbFromUnicode)
    Put #nFileNum, , byteData
    Close nFileNum

Strings in VBA are Unicode so we use Strconv() to strip the high order byte, so just create a string of your text, and use above method.

Hex view