ExcelのVBAでCSVを読み込むと文字化けするときの対処｜Open文で読めないUTF-8をADODB.Streamで文字コードを指定して読む

VBAで Open 文と Line Input # を使ってCSVを読むと、日本語が「譌･譛ｬ」のような意味の通らない文字に変わることがあります。英数字は正しく読めるのに、日本語だけが化けるのが典型的な症状です。

多くの場合、原因はUTF-8で保存されたCSVを、Shift_JISとして読んでいることです。この記事では、原因の確かめ方、BOMの有無の見分け方、ADODB.Stream で文字コードを指定して読む手順、読み込んだ後の確認方法を説明します。

なぜOpen文で読むと文字化けするのか

VBAの文字列は、内部ではUnicodeで扱われます。Line Input # でファイルを読むとき、VBAはファイルのバイト列を、Windowsのシステム既定の文字コードとして解釈して文字列に変換します。日本語版Windowsでは、この既定の文字コードは通常Shift_JIS系（CP932）です。

Open 文には、文字コードを指定する引数がありません。そのため、次のように結果が分かれます。

なお、Windowsの地域設定で「ワールドワイド言語サポートで Unicode UTF-8 を使用」（ベータ版の設定）が有効になっていると、既定の文字コードが変わります。この場合、同じマクロでもPCによって結果が変わることがあります。ある人のPCでは読めるのに別の人のPCでは化ける、という場合は、この設定の違いも疑ってください。

症状から状態を見分ける

BOMの有無を確かめる

BOMは、UTF-8のファイルの先頭に付くことがある EF BB BF の3バイトです。BOMがあれば、そのファイルはUTF-8だとほぼ判断できます。ただし、BOMがない場合は、UTF-8（BOMなし）なのかShift_JISなのかを、BOMだけでは判断できません。

先頭の3バイトを調べる関数の例です。

Function HasUtf8Bom(ByVal filePath As String) As Boolean
    Dim fileNo As Integer
    Dim b(0 To 2) As Byte

    fileNo = FreeFile
    Open filePath For Binary Access Read As #fileNo
    If LOF(fileNo) >= 3 Then
        Get #fileNo, 1, b
        HasUtf8Bom = (b(0) = &HEF And b(1) = &HBB And b(2) = &HBF)
    End If
    Close #fileNo
End Function

BOMがない場合は、次の方法で文字コードを確かめます。

• CSVを出力したシステムの仕様や設定画面を見る：出力時の文字コードが選べる場合や、仕様書に書かれている場合があります。
• テキストエディタで開く：メモ帳や多くのテキストエディタでは、画面下部などに文字コードが表示されます。表示される場所や表記はエディタやバージョンによって異なります。
• 両方の文字コードで読んでみる：日本語が正しく表示されるほうを採用します。ただし、この方法で判定を自動化すると誤判定することがあるため、出力元の仕様が分かるならそちらを優先します。

ADODB.Streamで文字コードを指定して読む

ADODB.Stream を使うと、文字コード（Charset）を指定してファイルを読めます。CreateObject で作れば、参照設定は不要です。なお、ADODBはWindows版Excelの機能で、Mac版Excelでは使えません。

Function ReadTextFile(ByVal filePath As String, ByVal charsetName As String) As String
    Dim stm As Object

    Set stm = CreateObject("ADODB.Stream")
    With stm
        .Type = 2                 ' テキストとして扱う
        .Charset = charsetName    ' "UTF-8" または "Shift_JIS"
        .Open
        .LoadFromFile filePath
        ReadTextFile = .ReadText(-1)   ' -1 で全体を読む
        .Close
    End With
End Function

UTF-8を指定して読むと、先頭のBOMは通常取り除かれます。ただ、もし残っていると1列目の見出しの比較がずれるため、読み込んだ後に念のため次の1行を入れておきます。

If Left(allText, 1) = ChrW(&HFEFF) Then allText = Mid(allText, 2)

読み込んでシートに書き出す例

BOMがあればUTF-8で読み、なければ定数で指定した文字コードで読みます。そのうえで、行とカンマで分けて「取込」シートへ書き出す例です。実行する前に、「取込」という名前のシートを作っておいてください。このマクロは「取込」シートの内容をすべて消してから書き出します。

Option Explicit

Const DEFAULT_CHARSET As String = "UTF-8"   ' BOMがないときの文字コード。Shift_JISなら "Shift_JIS"

Sub ImportCsvWithCharset()
    Dim filePath As Variant
    Dim charsetName As String
    Dim allText As String
    Dim lines As Variant
    Dim fields As Variant
    Dim ws As Worksheet
    Dim i As Long, j As Long

    filePath = Application.GetOpenFilename("CSVファイル (*.csv),*.csv")
    If VarType(filePath) = vbBoolean Then Exit Sub

    If HasUtf8Bom(CStr(filePath)) Then
        charsetName = "UTF-8"
    Else
        charsetName = DEFAULT_CHARSET
    End If

    allText = ReadTextFile(CStr(filePath), charsetName)
    If Left(allText, 1) = ChrW(&HFEFF) Then allText = Mid(allText, 2)
    If Len(allText) = 0 Then
        MsgBox "ファイルが空です。"
        Exit Sub
    End If

    ' 文字コードが合っていないと置換文字が入ることがある
    If InStr(allText, ChrW(&HFFFD)) > 0 Then
        MsgBox "置換文字が含まれています。文字コードの指定（" & charsetName & "）を確認してください。"
        Exit Sub
    End If

    ' 改行をLFにそろえ、末尾の改行を1つ取り除いてから行に分ける
    allText = Replace(allText, vbCrLf, vbLf)
    If Right(allText, 1) = vbLf Then allText = Left(allText, Len(allText) - 1)
    lines = Split(allText, vbLf)

    Set ws = ThisWorkbook.Worksheets("取込")
    ws.Cells.Clear
    ws.Cells.NumberFormat = "@"   ' 先頭の0が消えないよう文字列で書き込む
    For i = 0 To UBound(lines)
        fields = Split(lines(i), ",")
        For j = 0 To UBound(fields)
            ws.Cells(i + 1, j + 1).Value = fields(j)
        Next j
    Next i

    MsgBox "文字コード：" & charsetName & vbCrLf & _
           "先頭行：" & lines(0) & vbCrLf & _
           "データ件数（見出しを除く）：" & UBound(lines)
End Sub

この例は、文字化けを解消する部分に絞った最小限の処理です。次のような場合は、この分け方では行や列がずれるため、別の処理が必要です。

• 値の中にカンマがあり、ダブルクォーテーションで囲まれている
• セル内改行を含む
• 改行がCRだけのファイル

また、1セルずつ書き込んでいるため、数万行を超えるファイルでは時間がかかります。

読み込んだ後に先頭行と件数を確かめる

文字化けが直ったように見えても、次の点を確認してから後続の処理に進みます。

1. 先頭行（見出し）が想定どおりか：1列目の見出しを、期待する列名と文字列で比べます。BOMが残っていると、見た目は同じでも一致しません。

If Split(lines(0), ",")(0) <> "顧客コード" Then
    MsgBox "1列目の見出しが想定と違います：" & Split(lines(0), ",")(0)
End If

2. 件数が元のデータと合っているか：UBound(lines) は、見出しを除いたデータの件数です（見出し行がある場合）。CSVを出力したシステムの件数と比べます。末尾に空行が複数あると、その分だけ多く数えられます。
3. 日本語を含む行を目で確認する：先頭の数行だけでなく、最終行や途中の行も見ます。一部の行だけ化けている場合は、ファイルの途中で文字コードが変わっている（別のファイルを連結しているなど）可能性があります。
4. 置換文字が出ていないか：上のコードのように ChrW(&HFFFD) を検索します。含まれていれば、指定した文字コードがファイルと合っていない可能性が高いです。

Shift_JISのファイルも混ざる場合

取り込むCSVに、UTF-8とShift_JISの両方が混ざることがあります。BOM付きのUTF-8とShift_JISだけなら、HasUtf8Bom の結果で読み分けられます。BOMなしのUTF-8も混ざる場合は、ファイル名やフォルダで出力元を区別し、出力元ごとに文字コードを決めておくほうが確実です。出力元のシステムで文字コードを選べるなら、どちらかにそろえてもらうのがいちばん簡単です。