Can I export excel data with UTF-8 without BOM?

Question

I export Microsoft Excel data by Excel Macro(VBScript). Because file is lua script, I export it as UTF-8. The only way I can make UTF-8 in Excel is using adodb.stream like this

set fileLua = CreateObject("adodb.stream") fileLua.Type = 2 fileLua.Mode = 3 fileLua.Charset = "UTF-8" fileLua.Open fileLua.WriteText("test") fileLua.SaveToFile("Test.lua") fileLua.flush fileLua.Close 

I want to make eliminate BOM from Test.lua but I don't know how. (Because Test.lua has some unicode text, I have to use UTF-8 format.)

Do you know how to make UTF-8 file without BOM in excel file? Thanks in advance.

Answer 1

I have also the same issue: have to export data from Excel (Office 2003, VBA6.5) to UTF-8 encoded file. Found the answer from your question ! Below my example where I also strip the BOM using trick #2 from boost's (thanks!) answer. I didn't get #1 working and never tried #3.

Sub WriteUTF8WithoutBOM()     Dim UTFStream As Object     Set UTFStream = CreateObject("adodb.stream")     UTFStream.Type = adTypeText     UTFStream.Mode = adModeReadWrite     UTFStream.Charset = "UTF-8"     UTFStream.LineSeparator = adLF     UTFStream.Open     UTFStream.WriteText "This is an unicode/UTF-8 test.", adWriteLine     UTFStream.WriteText "First set of special characters: öäåñüûú€", adWriteLine     UTFStream.WriteText "Second set of special characters: qwertzuiopõúasdfghjkléáûyxcvbnm\|Ä€Í÷×äðÐ[]í³£;?¤>#&@{}<;>*~¡^¢°²`ÿ´½¨¸0", adWriteLine      UTFStream.Position = 3 'skip BOM      Dim BinaryStream As Object     Set BinaryStream = CreateObject("adodb.stream")     BinaryStream.Type = adTypeBinary     BinaryStream.Mode = adModeReadWrite     BinaryStream.Open      'Strips BOM (first 3 bytes)     UTFStream.CopyTo BinaryStream      'UTFStream.SaveToFile "d:\adodb-stream1.txt", adSaveCreateOverWrite     UTFStream.Flush     UTFStream.Close      BinaryStream.SaveToFile "d:\adodb-stream2.txt", adSaveCreateOverWrite     BinaryStream.Flush     BinaryStream.Close End Sub 

The ADO Stream Object reference I used.

Answer 2

If anyone else is struggling with the adTypeText constant, you need to include "Microsoft ActiveX Data Objects 2.5 Object Library" under Tools->References.