VBA : save a file with UTF-8 without BOM

Question

it's probably sthg simple, here is what I tried :

 Set objStream = CreateObject("ADODB.Stream")
 Set objStreamNoBOM = CreateObject("ADODB.Stream")

 With objStream
        .Open
        .Charset = "UTF-8"
        .WriteText "aaaaaa"
        .Position = 0
    End With

    With objStreamNoBOM
      '.Charset = "Windows-1252"   ' WORK
       .Charset = "UTF-8"          ' DOESN'T WORK!!
       .Open
       .Type = 2
       .WriteText objStream.ReadText
       .SaveToFile "toto.php", 2
       .Close
    End With
    objStream.Close

if the charset is UTF-8, then there is ï» at the beginning of the file.

Any idea on how to save a file with UTF-8 and without BOM?

score 36 · Accepted Answer · edited Oct 23 '17 at 03:53

In the best of all possible worlds the Related list would contain a reference to this question which I found as the first hit for "vbscript adodb.stream bom vbscript site:stackoverflow.com".

Based on the second strategy from boost's answer:

Option Explicit

Const adSaveCreateNotExist = 1
Const adSaveCreateOverWrite = 2
Const adTypeBinary = 1
Const adTypeText   = 2

Dim objStreamUTF8      : Set objStreamUTF8      = CreateObject("ADODB.Stream")
Dim objStreamUTF8NoBOM : Set objStreamUTF8NoBOM = CreateObject("ADODB.Stream")

With objStreamUTF8
  .Charset = "UTF-8"
  .Open
  .WriteText "aÄö"
  .Position = 0
  .SaveToFile "toto.php", adSaveCreateOverWrite
  .Type     = adTypeText
  .Position = 3
End With

With objStreamUTF8NoBOM
  .Type    = adTypeBinary
  .Open
  objStreamUTF8.CopyTo objStreamUTF8NoBOM
  .SaveToFile "toto-nobom.php", adSaveCreateOverWrite
End With

objStreamUTF8.Close
objStreamUTF8NoBOM.Close

Evidence:

chcp
Active code page: 65001

dir
 ...
15.07.2015  18:48                 5 toto-nobom.php
15.07.2015  18:48                 8 toto.php

type toto-nobom.php
aÄö

Great!. The first SaveToFile is not relly needed, a .Flush would do the job. To test the code, save the it as UTF16-LE in Notepad or the test string will not be garbled by vbs when it reads the source.. — Antoni Gual Via, Jul 31 '20 at 10:57

score 4 · Answer 2 · answered Jul 15 '15 at 17:06

I knew that the Scripting File System Object's stream inserted a Byte Order Mark, but I haven't seen that with the ADODB Stream.

Or at least, not yet: I rarely use the ADODB stream object...

But I do remember putting this remark into some code a few years ago:

'   ****   WHY THIS IS COMMENTED OUT   **** **** **** **** **** **** **** ****
'
'   Microsoft ODBC and OLEDB database drivers cannot read the field names from
'   the header when a unicode byte order mark (&HFF & &HFE) is inserted at the
'   start of the text by Scripting.FileSystemObject 'Write' methods. Trying to
'   work around this by writing byte arrays will fail; FSO 'Write' detects the
'   string encoding automatically, and won't let you hack around it by writing
'   the header as UTF-8 (or 'Narrow' string) and appending the rest as unicode
'
'   (Yes, I tried some revolting hacks to get around it: don't *ever* do that)
'
'   **** **** **** **** **** **** **** **** **** **** **** **** **** **** ****
'
'    With FSO.OpenTextFile(FilePath, ForWriting, True, TristateTrue)
'        .Write Join(arrTemp1, EOROW)
'        .Close
'    End With ' textstream object from objFSO.OpenTextFile
'
'   **** **** **** **** **** **** **** **** **** **** **** **** **** **** ****

You can tell I was having a bad day.

Next, using prehistoric PUT commands from the days before file-handling had emerged from the primordial C:

'   **** WHY WE 'PUT' A BYTE ARRAY INSTEAD OF A VBA STRING VARIABLE  **** ****
'
'       Put #hndFile, , StrConv(Join(arrTemp1, EOROW), vbUnicode)
'       Put #hndFile, , Join(arrTemp1, EOROW)
'
'   If you pass unicode, Wide or UTF-16 string variables to PUT, it prepends a
'   Unicode Byte Order Mark to the data which, when written to your file, will
'   render the field names illegible to Microsoft's JET ODBC and ACE-OLEDB SQL
'   drivers (which can actually read unicode field names, if the helpful label
'   isn't in the way). However, the 'PUT' statements writes a Byte array as-is
'
'   **** **** **** **** **** **** **** **** **** **** **** **** **** **** ****

So there's the code that actually does it:

Dim arrByte() As Byte
Dim strText   As String
Dim hndFile   As String


    strText = "Y'all knew that strings are actually byte arrays?"
    arrByte = strText 

    hndFile = FreeFile
    Open FilePath For Binary As #hndFile

    Put #hndFile, , arrByte
    Close #hndFile

    Erase arrByte

I'm assuming that strText is actually UTF-8. I mean, we're in VBA, in Microsoft Office, and we absolutely know that this is always going to be UTF-8, even we use it in a foreign country...

...Right?

thanks anyway for your answer Nile, it works finally with the solution of Ekkehard.Horner — Julien, Jul 15 '15 at 19:05
AFAIK this results in *UTF-16* encoding (supported in all current versions of Windows). — T.M., Aug 27 '19 at 18:38
@T.M - best check that: separately, in the VBA IDE's exported text, and in the text displayed on the worksheet itself. — Nigel Heffernan, Aug 28 '19 at 13:51
@NigelHeffernan, thx for feedback to a possible version issue (?); actually the Notepad editor in my newly installed Office 2019 64bit shows *UTF-16LE* as current saving option for the exported text so I guess it *is* UTF-16 encoded. — T.M., Aug 28 '19 at 16:14
Thanks for this excellent simple answer. From the description of the `Put` command, I never would have guessed `Put #hndFile, , arrByte` would have worked . **NB: You better add `On Error Resume Next` `Kill FilePath` `On Error GoTo 0` because the code as written will overwrite the beginning of `FilePath` with `strText`, but will not touch the rest of the contents of `FilePath`.** — Rocky Scott, Sep 20 '22 at 14:12

VBA : save a file with UTF-8 without BOM

2 Answers2

Linked