[Excel VBA] No More Mojibake! How to Export CSV Files in UTF-8 (ADODB.Stream)

Have you ever experienced text displaying as “???” (garbled text) when uploading a CSV file created in Excel VBA to a web system or opening it on a PC with a different OS?

This “Mojibake” occurs because the character encoding standardly output by Excel (such as Shift_JIS) differs from the character encoding expected by the target system (such as UTF-8).

In this article, I will explain a reliable method to solve this problem by using the ADODB.Stream object in VBA to specify the character encoding as “UTF-8” when exporting CSV files.

What is ADODB.Stream?

ADODB.Stream is an object used in VBA to flexibly handle data streams like text and binary data. Although it is part of the database connection library (ADODB), it can be used for file operations independently of databases.

The biggest advantage of using this is that you can freely specify the file’s character encoding (Charset), making it the most powerful tool for creating files in UTF-8 format.

Complete VBA Code

Below is the complete code that uses ADODB.Stream to export a CSV file in UTF-8 format.

Sub ExportCsvAsUtf8()

    ' Declare variables
    Dim utf8CsvPath As String
    Dim sourceDataRange As Range
    Dim i As Long
    Dim rowDataArray As Variant

    ' --- Settings ---
    ' Specify the cell range to export
    Set sourceDataRange = ThisWorkbook.Worksheets("Sheet1").Range("B2:F10")
    ' --- End Settings ---
    
    ' Specify the path and filename for the output CSV
    utf8CsvPath = ThisWorkbook.Path & "\UTF8_ExportData.csv"

    ' Create and process the ADODB.Stream object
    With CreateObject("ADODB.Stream")
        
        ' --- Stream Settings ---
        .Type = 2 ' 2 is adTypeText. Set mode to handle text data
        .Charset = "UTF-8" ' Set character encoding to UTF-8
        .Open ' Open the stream
        
        ' --- Writing Data ---
        For i = 1 To sourceDataRange.Rows.Count
            ' Get values for one row and convert to a 1D array using Transpose twice
            rowDataArray = sourceDataRange.Rows(i).Value
            rowDataArray = WorksheetFunction.Transpose(WorksheetFunction.Transpose(rowDataArray))
            
            ' Convert to comma-separated string with Join and write to stream with a new line
            .WriteText Join(rowDataArray, ","), 1 ' 1 is adWriteLine. Write with a new line
        Next i
        
        ' --- Save to file and cleanup ---
        .SaveToFile utf8CsvPath, 2 ' 2 is adSaveCreateOverWrite. Overwrite if file exists
        .Close ' Close the stream
        
    End With
    
    MsgBox "Exported CSV file in UTF-8 format."

End Sub

Explanation of Key Points

1. Creating the ADODB.Stream Object

With CreateObject("ADODB.Stream")
    ' ...
End With

Use CreateObject("ADODB.Stream") to generate the object. Using a With block simplifies the description of properties and methods starting with . thereafter.

2. Stream Settings (.Type, .Charset)

.Type = 2 ' 2 is adTypeText. Set mode to handle text data
.Charset = "UTF-8" ' Set character encoding to UTF-8
.Open ' Open the stream

Before writing data to the stream, you set what kind of data to handle and which character encoding to use.

• .Type = 2: Sets the data type handled by the stream to “Text” (equivalent to the constant adTypeText).
• .Charset = “UTF-8”: Sets the character encoding of this stream to “UTF-8”. This is the core part to prevent garbled text.
• .Open: Once settings are complete, open the stream to make it writable.

3. Writing Data (.WriteText)

.WriteText Join(rowDataArray, ","), 1

The code retrieves data one row at a time in a For loop and writes it to the stream using the .WriteText method.

• The first argument specifies the string to write (a comma-separated string created with the Join function).
• The second argument, 1, is an option (adWriteLine) that automatically adds a newline code to the end of the string. This ensures that text is generated with proper line breaks for each row.

4. Saving to File and Cleanup (.SaveToFile, .Close)

.SaveToFile utf8CsvPath, 2
.Close

Once all data has been written to the stream in memory, save it as a file using the .SaveToFile method.

• The second argument, 2, is an option (adSaveCreateOverWrite) to overwrite the file if a file with the same name already exists.
• Finally, use .Close to close the stream and release resources. This is a mandatory step.

Summary

To create CSV files in Excel VBA without garbled text, using the ADODB.Stream object is the most reliable and recommended method.

It might look a bit complex, but just by specifying .Charset = "UTF-8", you can create highly reliable data files for integration with web systems and modern applications. If you are struggling with garbled text, please try this method.