エクセルVBAでBOM無しのUTF-8でCSVファイルなどを出力する方法

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでデータを様々なファイル形式に書き出す方法についてお伝えしているシリーズ、今回で最終回となります。

前回は、こちらの記事。

エクセルVBAで文字コードUTF-8のCSVファイルを書き出す方法

エクセルのデータをファイルに書き出す方法シリーズです。今回は、エクセルVBAでADODB.Streamオブジェクトを使ってUTF-8の文字コードのCSVファイルを書き出す方法についてお伝えします。

tonari-it.com

例えばCSVなどの形式で保存するときに、その文字コードをUTF-8にして書き出す方法についてお伝えしました。

今回はその発展形。エクセルVBAでBOMなしのUTF-8にてCSVファイルを書き出す方法についてお伝えしていきたいと思います。

では、よろしくお願いいたします！

そもそもBOMとは何のこと？

BOMはボムと読みまして、Byte Order Markの略です。

Wikipediaでは

プログラムがテキストデータを読み込む時、その先頭の数バイトからそのデータがUnicodeで表現されていること、また符号化形式（エンコーディング）としてどれを使用しているかを判別できるようにしたものである。
引用：Wikipedia-バイトオーダーマーク

などと説明をされています。

具体的には、BOMありの場合は通常のエディタではわからない3バイトの情報（0xEF 0xBB 0xBF）がファイルの先頭に追加されているのだそうです。

それがあるおかげで、そのテキストデータがどういった文字コードを使っているのかが明らかになるはずのものなのですが、悲しいかな世の中はそうなってはいないようで

『UTF-8 BOMあり』で保存したfunctions.phpをWordPressにアップロードすると上記のようなエラーが出ました。このエラーが出る原因の1つに『BOM』があります。

と紹介されている通り、逆にBOMありUTF-8でエラーが発生することがあります。

従いまして、Webサイトを作る場合、BOM無しで保存したファイルを使うというのが一般的となっています。

前回作成したCSVファイルはBOMありのUTF-8

冒頭で紹介した前回の記事で、文字コードUTF-8でCSVファイルを書き出すプログラムを紹介しました。

プログラムはこちらです。

Sub writeCSV_utf8()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim csvFile As String
csvFile = ActiveWorkbook.Path & "\data_utf8.csv"

'ADODB.Streamオブジェクトを生成
Dim adoSt As Object
Set adoSt = CreateObject("ADODB.Stream")

Dim strLine As String
Dim i As Long, j As Long
i = 1

With adoSt
    .Charset = "UTF-8"
    .LineSeparator = adLF
    .Open

    Do While ws.Cells(i, 1).Value <> ""

        strLine = ""

        j = 1
        Do While ws.Cells(i, j + 1).Value <> ""

            strLine = strLine & ws.Cells(i, j).Value & ","
            j = j + 1

        Loop

        strLine = strLine & ws.Cells(i, j).Value

        .WriteText strLine, adWriteLine

        i = i + 1

    Loop
     
    .SaveToFile csvFile, adSaveCreateOverWrite
    .Close

End With

MsgBox "data_utf8.csvに書き出しました"

End Sub

しかし、もう読者の皆様はお察しの通り、残念ながらこのプログラムで作成したUTF-8のCSVファイルはBOMありとなっています。

試しにTeraPadなどのエディタでこのファイルを開きますと

UTF-8とありますが、これはBOMありのUTF-8です。

BOM無しのUTF-8は「UTF-8N」というように「N」がついているほうです。

このように、特に何もせずエクセルVBAでUTF-8でテキストファイルを作成するとBOMありになってしまいます。

ちなみに、Windowsデフォルトの「メモ帳」でもUTF-8保存した場合はBOMありになります。

Microsoftさん…ここでも我が道を行っていますよね…

BOMなしのUTF-8で出力する

BOMなしのUTF-8で出力する場合は、もうひと手間プログラムに手を加える必要があります。

上記プログラムでいうと、40行目と42行目の間に以下プログラムを追加します。

    .Position = 0 'ストリームの位置を0にする
    .Type = adTypeBinary 'データの種類をバイナリデータに変更
    .Position = 3 'ストリームの位置を3にする

    Dim byteData() As Byte '一時格納用
    byteData = .Read 'ストリームの内容を一時格納用変数に保存
    .Close '一旦ストリームを閉じる（リセット）

    .Open 'ストリームを開く
    .Write byteData 'ストリームに一時格納したデータを流し込む

新たに出てきたADODB.Streamオブジェクトのプロパティとメソッドについてまとめました。

Position：Streamオブジェクトの先頭からの位置を指定します。単位はバイトです。後述のTypeメソッドを実行する場合はPositionを0に設定しておく必要があります。
Type：データの種類を指定します。デフォルトはadTypeText（テキスト）。
Read：Streamオブジェクトからデータを読み取ります。デフォルトではすべてのデータを取得します。

BOMデータはバイト型のデータで3バイト分あります。

そのデータを飛ばすために、Typeプロパティで扱うデータをバイナリデータに変更をしてから、Positionプロパティでその位置を3バイト目に移動という処理をしています。

今いる位置から全てを読み取って、byteDataという変数に一時保存をします。

Streamオブジェクトを一旦閉じて再度開き、そこからWriteメソッドでbyteData内のデータを戻すという処理です。

実行結果

こちらを実行して出力されたファイルをTeraPadで開くと

きちんとBOM無しUTF-8で出力されていることがわかります。

BOMなしのUTF-8でCSVファイルを書き出すプログラム

最後に全体のプログラムをまとめておきますね。

Sub writeCSV_utf8N()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim csvFile As String
csvFile = ActiveWorkbook.Path & "\data_utf8.csv"

'ADODB.Streamオブジェクトを生成
Dim adoSt As Object
Set adoSt = CreateObject("ADODB.Stream")

Dim strLine As String
Dim i As Long, j As Long
i = 1

With adoSt
    .Charset = "UTF-8"
    .LineSeparator = adLF
    .Open

    Do While ws.Cells(i, 1).Value <> ""

        strLine = ""

        j = 1
        Do While ws.Cells(i, j + 1).Value <> ""

            strLine = strLine & ws.Cells(i, j).Value & ","
            j = j + 1

        Loop

        strLine = strLine & ws.Cells(i, j).Value

        .WriteText strLine, adWriteLine

        i = i + 1

    Loop

    .Position = 0 'ストリームの位置を0にする
    .Type = adTypeBinary 'データの種類をバイナリデータに変更
    .Position = 3 'ストリームの位置を3にする

    Dim byteData() As Byte '一時格納用
    byteData = .Read 'ストリームの内容を一時格納用変数に保存
    .Close '一旦ストリームを閉じる（リセット）

    .Open 'ストリームを開く
    .Write byteData 'ストリームに一時格納したデータを流し込む
    .SaveToFile csvFile, adSaveCreateOverWrite
    .Close

End With

End Sub

まとめ

エクセルVBAでBOMなしのUTF-8にてCSVファイルを書き出す方法についてお伝えしました。

UTF-8で出力する場合は基本的にこちらのプログラムを採用頂くほうが無難だと思います。

さて、これまでエクセルVBAを用いて様々なテキストファイルの出力をしてきましたが、いかがだったでしょうか？

エクセルは実務上はデータベース的に使われいることも多いと思うので、他のサービスにデータを持ち出す際にお役に立つことと思います。

どうぞご活用頂ければ幸いです。

連載目次：エクセルVBAでCSVや様々なテキストファイルを出力する

多くの企業では様々なデータをエクセル形式で保持しています。そのデータ群をCSVやHTMLなどで有効利用できたら便利ですよね？このシリーズでは、エクセルデータをテキストをはじめ様々な形式のファイルに出力する方法についてお伝えしています。

1. エクセルVBAでテキストファイルに書き出す最も簡単なプログラム
2. エクセルVBAでエクセルシート上のデータをCSVファイルに書き出す
3. エクセルの表をHTMLのtableタグに変換して出力するVBAマクロ
4. エクセルVBAでセルが結合されているかどうか、またそのセルの数を判定する方法
5. エクセルVBAで文字コードUTF-8のCSVファイルを書き出す方法
6. エクセルVBAでBOM無しのUTF-8でCSVファイルなどを出力する方法