About This Article
This article was created using an automated generation workflow leveraging generative AI. It reviews Microsoft Learn’s ADO Stream / Charset specifications and organizes them as a TIP for saving UTF-8 text from VBA.Validation Status:📘 Official specifications verified · VBA sample implementation completed · Excel actual device unverified
Saving UTF-8 Without Garbled Characters in VBA — Using ADODB.Stream Without Reference Settings
When exporting CSV or JSON from VBA, issues such as “Japanese characters became garbled” or “the recipient system required UTF-8” can sometimes occur.
The Stream object available in ADO on Windows can save text by specifying Charset.
No Reference Settings Needed with Late Binding
Dim stream As Object
' Since it uses Late Binding via CreateObject,
' you can test this without adding ADO to the VBE reference settings.
Set stream = CreateObject("ADODB.Stream")
' Type=2 is an ADO constant value meaning "treat as text".
stream.Type = 2
stream.Open
' Return Position to the beginning before setting Charset.
' Position is important when changing Charset in ADO Stream.
stream.Position = 0
stream.Charset = "utf-8"
' Write the VBA String.
stream.WriteText "日本語とEnglish"
' The second argument = 2 specifies overwriting a file with the same name.
stream.SaveToFile "C:Tempsample.txt", 2
' Finally, close the Stream opened by the COM object.
stream.Close
Type = 2 is a text Stream, and SaveToFile with the second argument 2 specifies overwriting an existing file.
Set Charset at Position=0
According to Microsoft’s ADO specifications, setting the Charset of an open Stream requires the current position to be at the beginning. Therefore, the sample also explicitly specifies Position = 0.
Be Aware That It Becomes UTF-8 with BOM
The method of using Charset = "utf-8" with ADODB.Stream generally treats the file as UTF-8 with BOM.
While this can be convenient for Excel and Windows-based tools, if an external system strictly requires “UTF-8 without BOM”, a separate process to remove the BOM is necessary.
Differences With/Without Reference Settings
If you add ADO via reference settings, you can explicitly type it as ADODB.Stream. On the other hand, using CreateObject("ADODB.Stream") as done here results in Late Binding, avoiding the need to increase reference settings on the workbook side.
flowchart LR
A[VBA文字列] --> B[ADODB.Stream]
B --> C[Charset=utf-8]
C --> D[SaveToFile]
D --> E[UTF-8テキスト]
コメント