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

by

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.

More Related Contents:

  1. Is it possible to force Excel recognize UTF-8 CSV files automatically?
  2. Excel to CSV with UTF8 encoding [closed]
  3. Run Excel Macro from Outside Excel Using VBScript From Command Line
  4. How to set recurring schedule for xlsm file using Windows Task Scheduler
  5. VBA : save a file with UTF-8 without BOM
  6. Export each sheet to a separate csv file
  7. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  8. Bad data selection using VBScript , on date field values
  9. How to use excel built-in constant in VBS [duplicate]
  10. How do I use awk under cygwin to print fields from an excel spreadsheet?
  11. Excel: Consecutive 3 Occurrences or more of a value in a string
  12. Error in finding last used cell in Excel with VBA
  13. Excel VBA, getting range from an inactive sheet
  14. How to use the Implements in Excel VBA
  15. What is the difference between dim and set in vba
  16. Change date format using substitute or replace
  17. VBA: Convert Text to Number
  18. Referencing Excel Userform Control Name Value From String (VBA)
  19. Refreshing all the pivot tables in my excel workbook with a macro
  20. Countif With Multiple OR Criteria
  21. Vlookup to copy color of a cell
  22. Excel Reference To Current Cell
  23. converting an Excel (xls) file to a comma separated (csv) file without the GUI
  24. Skipping rows when importing Excel into SQL using SSIS 2008
  25. Delay macro to allow events to finish
  26. How to programmatically code an ‘undo’ function in Excel-Vba? [duplicate]
  27. Extract URL From Excel Hyperlink Formula
  28. What is the different between IsNull, IsEmpty, =Empty, and an empty string ie “” and why might I use variants
  29. How to detect if user select cancel InputBox VBA Excel
  30. Change HTML email body font type and size in VBA

Leave a Comment