Excel-VBA UTF-8形式のファイルを読み込み編集する

こんにちは！健史です。

「UTF8 BOM無し」のCSV形式のファイルをExcelで開くと、漢字部分が文字化けします。

そんなときは、サクラエディタなどで開いて「SJIS形式」や「UTF8 BOM付き」で保存することで、Excelで閲覧できるようになります。

今回プログラムでExcelデータに変換する処理を作成しました。

用途に合わせてご利用いただければと思います。

尚この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。

Excel-VBAの構造、起動方法、実行方法などを理解されていない方は、以下の記事を参照しながら一度やってみてから対応されることをお勧めします。

こんにちは！健史です。パソコンをお使いの方の多くは、表計算ソフトExcelを利用されていると思います。この記事は、Exceは知っているけれど・Excel-VBAって何？・Excel-VBAは取っ付きにくい！・Excel-VBAって難しそう...

実行中に発生したエラーの対処法は、上記記事の2.エラーが発生したときの対処法に記載しています。(この段落のリンクから直接遷移します)

目　次　

処理イメージ
プログラム
行単位に取り込む
最後に

処理イメージ

[UTF8 BOM無し]のCSVを作成したファイルをExcelで開いてみると、漢字の[ＡＢＣ]、[あいうえお]は文字化けしています。

・サクラエディタで作成したデータ

・Excelで開いた状態

一度だけの処理であれば、サクラエディタなどで開いて[SJIS形式]や[UTF8 BOM付き]に変換すればよいでしょう。

しかし定常的に複数回処理する場合には面倒です。

上記ファイルをプログラムで以下のように編集してみました。

・変換した結果

A列は、読み込んだデータをそのまま出力しています。

B列目は、改行単位のデータを出力しています。

C列目以降は、行内のデータをカンマ区切りしたデータで出力しています。

プログラム

実行手順の概要です。
1.マクロのシート１を設定シートとして、入力ファイル(UTF8)、出力ファイル(SJIS)を指定します。

2.マクロを実行すると、入力ファイルを読み込み、変換したデータを出力ファイルに作成します。

プログラムです。

    Dim strTxt1 As String
    Dim tblTxt1, tblTxt2 As Variant
    Dim ix1, iy1 As Long
    Dim Excel0, Excel1 As Workbook
'＜初期処理＞
    Set Excel0 = ActiveWorkbook
    Set Excel1 = Workbooks.Add
'＜UTF8データの変換処理＞
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open
        .LoadFromFile Excel0.Sheets(1).Cells(1, 2)
        strTxt1 = .ReadText(-1)
        .Close
        Excel1.Sheets(1).Cells(1, 1) = strTxt1
        tblTxt1 = Split(strTxt1, vbLf) '種類；vbCrLf,vbLf,vbCr
        For ix1 = 0 To UBound(tblTxt1)
            Excel1.Sheets(1).Cells(ix1 + 1, 2) = tblTxt1(ix1)
            tblTxt2 = Split(tblTxt1(ix1), ",")
            For iy1 = LBound(tblTxt2) To UBound(tblTxt2)
                Excel1.Sheets(1).Cells(ix1 + 1, 3 + iy1) = tblTxt2(iy1)
            Next
        Next
    End With
'＜出力ファイル名で保存、終了＞
    Excel1.SaveAs Excel0.Sheets(1).Cells(2, 2)
    MsgBox "処理終了！"
    Application.Quit

処理概要です。

1.データ全体をstrTxt1に取り込む

2.strTxt1のデータをSplit命令で改行単位に分割し、配列・可変属性のValiant項目であるtblTxt1に格納する

3.配列tblTxt1の先頭0番目のデータをカンマ区切りされた項目を一つひとつに分割し、可変配列・可変属性のValiant項目であるtblTxt2に格納する

補足です。

・[With CreateObject("ADODB.Stream")、End With]と[.]で始まるもの
[With CreateObject("ADODB.Stream")、End With]や[.]で始まる[.Charset = "UTF-8"]、[.Open]などは決まりごとです。

「太陽が東から登り西へ沈むこと」や「三平方の定理」と同じように決まったことで「なぜ」と問われても答えられないのと同じように、この通りに書くしかありません。

・全体を取り込む指定
".ReadText(-1)"の"-1"は全体を取り込む指定です。

・配列(テーブル)は0から始まる
配列(テーブル)の添字の既定の下限を宣言する設定[Option Base n]が、既定値では[Option Base 0]になっています。

例えば[Dim strTxt1(2) As String]と定義すると、実際にはstrTxt1(0)～strTxt1(2)までの3個確保されます。

試しに、以下のプログラムでは正常終了しますが、

Sub test1()
    Dim str(2) As String
    Dim ix1 As Long
    str(0) = "00"
    str(1) = "01"
    str(2) = "02"
    For ix1 = 0 To 2
        MsgBox str(ix1)
    Next
End Sub

以下のプログラムは異常終了します。

Option Base 1
Sub test1()
    Dim str(2) As String
    Dim ix1 As Long
    str(0) = "00"
    str(1) = "01"
    str(2) = "02"
    For ix1 = 0 To 2
        MsgBox str(ix1)
    Next
End Sub

既定値のままSplit命令で可変配列・可変属性を受け側にすると0番目からセットされます。

行単位に取り込む

上記は全体を取り込み、その後、行データ→カンマ区切りデータを抽出していくプログラムでした。

行単位に取り込み、その後、カンマ区切りデータを抽出するプログラムです。

出力イメージです。

全体取り込みがなくなり行単位に取り込んでいくので、上記のA列がなくなったイメージです。

    Dim strTxt1 As String
    Dim tblTxt1 As Variant
    Dim ix1, iy1 As Long
    Dim Excel0, Excel1 As Workbook
'＜初期処理＞
    Set Excel0 = ActiveWorkbook
    Set Excel1 = Workbooks.Add
'＜UTF8データの変換処理＞
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open
        .LineSeparator = 10 '種類；-1:CrLf(既定値),10:Lf,13:Cr
        .LoadFromFile Excel0.Sheets(1).Cells(1, 2)
        ix1 = 0
        Do Until .EOS
            strTxt1 = .ReadText(-2)
            ix1 = ix1 + 1
            Excel1.Sheets(1).Cells(ix1, 1) = strTxt1
            tblTxt1 = Split(strTxt1, ",")
            For iy1 = LBound(tblTxt1) To UBound(tblTxt1)
                Excel1.Sheets(1).Cells(ix1, 2 + iy1) = tblTxt1(iy1)
            Next
        Loop
        .Close
    End With
'＜出力ファイル名で保存、終了＞
    Excel1.SaveAs Excel0.Sheets(1).Cells(2, 2)
    MsgBox "処理終了！"
    Application.Quit

1.行単位のデータをstrTxt1に取り込む

2.strTxt1のデータをSplit命令でカンマ区切りされた項目を一つひとつに分割し、可変配列・可変属性のValiant項目であるtblTxt2に格納する

補足です。
・行単位に取り込む指定
".ReadText(-2)"の"-2"は行単位に取り込む指定です。

この場合は'どこまで取り込むか'という条件が必要になりますが、ループ処理[Do Until .EOS]の[Until .EOS]が「最後の行まで」という条件になります。

・改行コードについて
今回作成したデータの改行コードはExcelでいうところのセル内改行(Alt+Enterで)のコードです。

改行コードは3種類あります。VBAで記述する場合、1行ずつ読み込む場合で指定する[LineSeparator]のコードと併せて説明します。

種類	VBA	LineSeparator	マーク	改行コード
CrLf	vbCrLf	-1(既定値)	↩	\r\n
Cr	vbCr	13	←	\r
Lf	vbLf	10	↓	\n

処理するファイルをサクラエディタなどで開き改行の種類を確認し、プログラムを変更する必要があります。

最後に

「UTF8 BOM無し」のデータを処理しなければならないときに戸惑いましたが、この記事を書くのに更に調べわからないことや疑問に思うことがわかるようになりました。

冒頭の"用途"とは、読み込んだデータそのまま、改行単位のデータ、カンマ区切りしたデータのうち「目的に応じて"どれか１つ"もしくは"複数"を利用していただければ」との考えです。