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

Question

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?

Maybe I am wrong but according to the [documentation](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/createtextfile-method) `fso.CreateTextFile("File.txt")` will create an ASCII file. An Unicode file will only be created with `fso.CreateTextFile("D:\TEMP\File.txt", , True)` but then with a BOM. — Storax, Aug 30 '22 at 19:11
Yes, you are right about the ASCII file. Any ideas about how to get rid of the BOM when using fso.CreateTextFile("D:\TEMP\File.txt", , True) — Per, Aug 30 '22 at 19:17
AFAIK that is not possible when using the file system object or at least I do not know how to get rid of the BOM in this case But how large is your file that other methods are too slow.? — Storax, Aug 30 '22 at 19:19
ok, do you have any other ideas on how to save a file without a BOM that would be faster than ADODB.Stream or do you perhaps know a way to speed up the time spent on saving a file when using ADODB.Stream? — Per, Aug 30 '22 at 19:21
Not really, maybe [this](https://stackoverflow.com/a/31436726/6600940) is of any help. — Storax, Aug 30 '22 at 19:24
I just tested the method described in the link above, unfortunately that saves the file in UTF-16, which does not work for my purpose. — Per, Aug 30 '22 at 19:39

score 0 · Accepted Answer · edited Sep 05 '22 at 08:06

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:

I should add that the following will also work:

    Dim strFile As String
    Dim nFileNum As Integer
    Dim stringData As String

    nFileNum = FreeFile

    strFile = "c:\myfile.txt"
    Open strFile For Output Access Write As nFileNum
    stringData = "this is a string for line #1" & vbCrLf & "this is a string for line#2"
    Print #nFileNum, stringData
    Close nFileNum

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

1 Answers1