Excel で UTF-8 の CSV ファイルを出力する VBA プログラム

Excel 標準の機能だけでは UTF-8 の CSV ファイルを出力することができません。メニューから［名前をつけて保存］すれば CSV 形式で保存することはできるのですが，残念ながらエンコーディングを指定することができません。ワークシートを CSV 形式で保存した場合，エンコーディングは強制的に Shift_JIS になってしまいます。

Excel で UTF-8 の CSV ファイルを出力するには VBA を使います。 ADO（ActiveX Data Objects）の ADODB.Stream オブジェクトを利用すれば，エンコーディングを指定してデータを読み書きすることができます。ただし，この方法で UTF-8 のデータを出力すると BOM が付加されるので，気になるようであればこれを削除する処理も考えてやらなくてはなりません。

ここでは，Excel VBA で ADODB.Stream オブジェクトを利用し，ワークシートの内容を UTF-8 エンコーディングで BOM のついていない CSV ファイルとして出力する方法を紹介します。

ライブラリの参照設定

デフォルトでは ADO のライブラリは読み込まれていないため，New でオブジェクトを生成することができず，Visual Basic Editor のコード補完機能なども働きません。オブジェクトの生成は CreateObject 関数で可能なのですが，クラスや定数にアクセスできないのは不便なので，まずはライブラリの参照設定をしましょう。

Visual Basic Editor のメニューから［ツール］→［参照設定］を選び，［参照可能なライブラリファイル］の中から "Microsoft ActiveX Data Objects x.x Library" にチェックを入れます。

f:id:amano41:20140423183351p:plain

いろいろバージョンがありますが，ADODB.Stream はバージョン 2.5 で導入されたので，それ以降のバージョンであれば OK です。ファイルを配付する予定がなければ最新のバージョンにチェックを入れておけばよいと思います。

なお，アドインなどとしてファイルを配布する場合には注意が必要です。配布先の環境に同じバージョンのライブラリが入っていないと実行時エラーが発生するからです。ファイルを配付する際には参照設定を外しておくのが安全です。この場合，オブジェクトは CreateObject 関数で動的に生成することになり，定数は MSDN などで調べて自分で定義する必要があります。

ADODB.Stream オブジェクト

まずは ADODB.Stream オブジェクトを生成します。

Dim outStream As ADODB.Stream
Set outStream = New ADODB.Stream

ADODB.Stream の Type プロパティでストリームの種類をテキストに設定し，Charset プロパティでエンコーディングを指定します。 MSDN によれば，Charset には一般的なエンコーディングをあらわす文字列が指定できるようです。今回は "UTF-8" を指定します。改行コードを指定したければ LineSeparator プロパティを設定します。

With outStream
    .Type = adTypeText
    .Charset = "UTF-8"
    .LineSeparator = adLF
End With

ストリームを開いたらデータ出力の準備は完了です。

outStream.Open

ワークシートの内容を出力

二重の For ループでワークシートを走査し，セルの値にカンマをつけて 1 行ずつ ADODB.Stream に流し込みます。以下の例では Cells(1, 1) から Cells(maxRow, maxCol) の範囲にデータがあるとしています。 maxRow と maxCol の値は Sheet オブジェクトの UsedRange プロパティを使えば簡単に求めることができます。

Dim r As Long
Dim c As Long
Dim line As String

'1 行ずつ処理
For r = 1 To maxRow

    '1 列目はカンマなし
    line = ActiveSheet.Cells(r, 1)
    
    '2 列目以降
    For c = 2 To maxCol
        line = line & "," & ActiveSheet.Cells(r, c)
    Next
    
    'r 行目のデータを Stream に出力
    outStream.WriteText line, adWriteLine
Next

BOM の削除

あとはストリームの内容をファイルに保存するだけなのですが，このまま出力すると BOM 付きのファイルになってしまいます。 BOM なしの UTF-8 にしたい場合には，もう一手間必要です。

BOM の削除は，ストリーム内の位置を調整して BOM の部分をスキップしてから出力することで実現します。 Position プロパティを 0 にして位置をストリームの先頭に戻し，Type プロパティでバイナリモードに変更してから，位置を 3 バイトに設定します。これでストリームの先頭にある BOM の分 3 バイトをスキップした状態になります。

outStream.Position = 0
outStream.Type = adTypeBinary
outStream.Position = 3

ADODB.Stream にはストリームの途中からファイルに書き出す機能はないので，この状態で一度新しいオブジェクトにコピーし，BOM を含まないストリームデータを用意します。

Dim csvStream As ADODB.Stream
Set csvStream = new ADODB.Stream

'バイナリモードで開く
csvStream.Type = adTypeBinary
csvStream.Open

'BOM の後からデータをコピー
outStream.CopyTo csvStream

ファイルへの書き出しはこのコピーの方で行います。

ファイルへの出力と後始末

SaveToFile メソッドでファイルに出力します。 MSDN の記述がわかりづらい（というより間違っているらしい）のですが，第二引数でファイルが存在した場合に上書きするかどうかを指定できます。ここでは adSaveCreateOverWrite で上書きを許可しています。規定値の adSaveCreateNotExist は「ファイルが存在しない場合のみ作成する」という意味（＝上書きしない）のようです。

Dim fileName As String
fileNme = ActiveSheet.Name & ".csv"
csvStream.SaveToFile fileName, adSaveCreateOverWrite

Open したストリームは必ず Close しなくてはなりません。

csvStream.Close
outStream.Close

出力された CSV ファイルをエディタなどで開いてみれば，きちんと UTF-8 で出力されていることがわかると思います。ちなみに，こうしてできた UTF-8 の CSV ファイルを再度 Excel で開くには，また少し工夫が必要だったりします。

参照設定をしない場合

ライブラリの参照設定をしない場合，ADODB.Stream が未定義の状態となるので，New によるインスタンスの生成ができません。この場合，以下のように CreateObject 関数を使って動的に生成することになります。 ADODB.Stream という型が使えないので，変数の型も Object 型になっていることに注意してください。

Dim outStream As Object
Set outStream = CreateObject("ADODB.Stream")

また，同様にライブラリで定義されている定数も利用できません。 MSDN などで値を調べて自分で定義する必要があります。以下はこのサンプルで使用した定数の値です。

'StreamTypeEnum
Const adTypeBinary = 1
Const adTypeText   = 2

'LineSeparatorsEnum
Const adCR   = 13
Const adCRLF = -1
Const adLF   = 10

'StreamWriteEnum
Const adWriteChar = 0
Const adWriteLine = 1

'SaveOptionsEnum
Const adSaveCreateNotExist  = 1
Const adSaveCreateOverWrite = 2