[Excel VBA] How to Read UTF-8 Files and Output to Cells Using ADODB.Stream

2025年12月29日

There are several ways to read files in Excel VBA. However, when you need to specify character encoding or require precise control over the data, using ADODB.Stream is the most effective method.

This article explains how to use the ADODB.Stream object to read a UTF-8 encoded CSV file and populate an Excel sheet with the data.

Scenario

Target File: data_utf8.csv (UTF-8 encoded)
Reading Method: Read line by line (Comma-separated/CSV format)
Output: Split each line and paste it into cells row by row

VBA Code: Reading with Stream and Outputting to Sheet

Sub LoadUTF8CSVWithStream()
    Dim bufferText As String
    Dim lines As Variant
    Dim rowParts As Variant
    Dim i As Long
    Dim targetCell As Range
    
    ' Read the UTF-8 file
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Type = 2 ' Text mode
        .Open
        .LoadFromFile ThisWorkbook.Path & "\data_utf8.csv"
        bufferText = .ReadText
        .Close
    End With
    
    ' Set the starting position for writing (A1 in this case)
    Set targetCell = ThisWorkbook.Worksheets(1).Range("A1")
    
    ' Split data by line (Line Feed)
    lines = Split(bufferText, vbCrLf)
    
    For i = 0 To UBound(lines)
        rowParts = Split(lines(i), ",")
        ' Paste the data by resizing the range based on the number of columns
        targetCell.Offset(i, 0).Resize(1, UBound(rowParts) + 1).Value = rowParts
    Next
End Sub

Key Points

CreateObject(“ADODB.Stream”): This is used to handle text file streams.
.Charset = “UTF-8”: Specifying this property ensures that UTF-8 files are read correctly without garbled text.
Split and Paste: The code splits the text into lines, then splits each line by commas.
Offset and Resize: These methods are combined to paste arrays into cells efficiently without selecting them.

When is This Method Useful?

Situation	Why it is Effective
Reading UTF-8 files accurately	It offers more flexibility with encoding than `Workbooks.OpenText`.
Preprocessing data	You can modify the text string in memory before pasting it to the sheet.
Custom cell layout	Using `Offset` and `Resize` allows you to place data exactly where needed.

Important Notes

ADODB.Stream is a COM object available in VBA. On some secure environments or specific PC configurations, library references might need checking.
This method generally handles UTF-8 with or without BOM. If text issues persist, consider using Scripting.FileSystemObject as an alternative.
If the file contains empty lines, Split() results will include empty strings. Adding error handling for empty rows will make the code more robust.

Summary

Using ADODB.Stream allows Excel VBA to read UTF-8 CSV files accurately without character encoding errors.

Since the data is loaded into memory first, it allows for programmatic manipulation and formatting before output. This is highly effective for data integration with external systems or creating standardized reports.

よかったらシェアしてね！