更新日：、 作成日：

VBA CSV ファイルの書き込みと保存

はじめに

Excel VBA マクロで CSV ファイルの書き込みと保存する方法を紹介します。

文字列をダブルクォーテーションで囲んだり、数値や日付の形式を指定できます。

区切り文字の指定や、文字コードを Shift_JIS や UTF-8 で保存できます。

CSV ファイルの読み込みについては「CSV ファイルの読み込み (QueryTables.Add)」をご覧ください。

CSV ファイルの書き込み

Excel を拡張子 .csv で保存すると、ダブルクォーテーションが付かなかったり、数値や日付の形式を変更できないなど、項目ごとの制御ができません。

このため、CSV 形式の文字列を手動で作成します。それを FileSystemObject を使ってファイルに書き込みます。

次のコードは、セルの値をカンマ区切りの文字列に結合するシンプルは方法です。

Dim csv As String  ' CSV に書き込む全データ
Dim line As String ' 1 行分のデータ

Dim region As Range
Set region = Range("A1").CurrentRegion ' データの範囲を自動取得

Dim row As Range
For Each row In region.Rows ' 行のループ

    line = ""
    Dim cell As Range
    For Each cell In row.Columns ' 列のループ

        ' カンマ区切りで結合
        Dim item As Variant
        item = cell.Value
        If line = "" Then
            line = item
        Else
            line = line & "," & item
        End If

    Next

    ' 行を結合
    If csv = "" Then
        csv = line
    Else
        csv = csv & vbCrLf & line
    End If

Next

作成した文字列を書き込むには次のようにします。

ファイルの書き込みについて詳しくは「ファイルの書き込み」をご覧ください。

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim ts As TextStream
Set ts = fso.OpenTextFile("D:\Tips.csv", ForWriting, True)

ts.Write (csv) ' 書き込み

ts.Close ' ファイルを閉じる

' 後始末
Set ts = Nothing
Set fso = Nothing

作成された CSV は次のように単純なカンマ区切りになります。

あああ,123,2000/01/02
いいい,456,2003/04/05
ううう,789,2006/07/08

データの範囲を取得

Set region = Range("A1:C3")            ' セル「A1 ～ C3」の範囲を取得
Set region = Range("A1").CurrentRegion ' セル「A1」を含むデータの範囲を取得
Set region = UsedRange                 ' シートで使用されている最大の範囲を取得

Range("A1").CurrentRegion を使って、セル「A1」を含むデータの固まりの範囲を簡単に取得できます。セルの場所を変更して任意の場所のセルの範囲を取得できます。この他のセルの範囲について詳しくは「セルを範囲指定して取得する」をご覧ください。

Dim ws As Worksheet
Set ws = ActiveSheet      ' 選択しているシート
Set ws = Sheets(1)        ' 左から 1 番目のシート
Set ws = Sheets("Sheet1") ' Sheet1 という名前のシート

Set region = ws.Range("A1").CurrentRegion ' そのシートの範囲を取得

シートを選択したいときは ws.Range("A1").CurrentRegion や ws.UsedRange のようにして、そのシートの範囲を取得できます。

Set region = Range("A1").CurrentRegion                     ' 範囲を取得
Set region = region.Offset(1, 0)                           ' 範囲を 1 行下にずらす
Set region = region.Resize(RowSize:=region.Rows.Count - 1) ' 範囲を 1 行分縮める

取得したデータの範囲に見出し行が含まれているときは region.Offset と region.Resize を使って取り除けます。セル「A1 ～ C3」の範囲が「B1 ～ C3」のようになります。

ダブルクォーテーションで囲む

item = """" & cell.Value & """"
"あああ","いいい","ううう"

ダブルクォーテーションを文字列で表すには "" のように 2 つ連続させます。これも文字なので、文字列として表すためにダブルクォーテーションで囲む必要があります。そのためダブルクォーテーション 1 文字をコードで表すには """" のように 4 つ連続させます。

CSV を読み込む側がダブルクォーテーションをどのように扱っているかが問題ですが、通常は文字列、数値、日付に関係なくすべての項目をダブルクォーテーションで囲むと思います。

数値の形式

item = Format(cell.Value, "000") ' 3 桁の前 0 の形式にする
001,012,123

数値を指定の形式に変換したいときは「Format 関数」を使います。

item = """" & Format(cell.Value, "#,##0") & """" ' 4 桁以上のときはカンマ区切りにする
"1,234","1,234,567","123"

数値の桁にカンマを入れたいときは、ダブルクォーテーションで囲む必要があります。

日付の形式

item = Format(cell.Value, "yyyy/m/d") ' 年月日の形式にする
2000/1/2,2003/4/5,2006/7/8

日付を指定の形式に変換したいときは「Format 関数」を使います。

項目の型を判別

Select Case cell.Column
Case 1 ' 文字列
    item = """" & cell.Value & """"

Case 2 ' 数値
    item = Format(cell.Value, "000") 

Case 3 ' 日付
    item = Format(cell.Value, "yyyy/m/d")

End Select
"あああ",012,2000/1/2

cell.Column から列番号を取得できます。その列により文字列か数値か日付かを分岐して、項目別の処理ができます。

区切り文字を変更

Dim delimiter As String
delimiter = ","   ' カンマ区切り
delimiter = vbTab ' タブ区切り
delimiter = ";"   ' セミコロン区切り
delimiter = " "   ' スペース区切り


line = line & delimiter & item

カンマで結合している個所を任意の 1 文字にすると、指定した区切り文字にできます。

文字コードの指定

Set ts = fso.OpenTextFile("D:\Tips.csv", ForWriting, True, TristateTrue)  ' ファイルを UTF-16 で開く
Set ts = fso.OpenTextFile("D:\Tips.csv", ForWriting, True, TristateFalse) ' ファイルを Shift_JIS で開く

VBA では基本的に Shift_JIS か UTF-16 にしか対応していません。それ以外の UTF-8 などの保存はできません。

ただし ADODB.Stream を使用すれば UTF-8 で保存できます。

Const adSaveCreateOverWrite = 2
Const adTypeBinary = 1
Const adTypeText = 2

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

With adoTemp
    .Charset = "UTF-8" ' UTF-8 を指定
    .Open
    .WriteText csv ' 作成した CSV 形式のデータを書き込み
    .Position = 0
    .Type = adTypeBinary
    .Position = 3
End With

With adoUTF8 ' BOM を取り除いて保存
    .Type = adTypeBinary
    .Open
    adoTemp.CopyTo adoUTF8
    .SaveToFile "D:\Tips.csv", adSaveCreateOverWrite ' CSV ファイルに保存
End With

adoTemp.Close
adoUTF8.Close

Set adoTemp = Nothing
Set adoUTF8 = Nothing

.WriteText csv で、作成した CSV 形式の文字列 (変数名 csv)を書き込んでいます。

.SaveToFile "D:\Tips.csv", adSaveCreateOverWrite で、指定したパスにファイルを作成します。

CSV ファイルの書き込みのまとめ

これまで紹介したことをまとめると次のコードになります。変数 csv に CSV 形式の文字列が作成されます。

Dim csv As String  ' CSV に書き込む全データ
Dim line As String ' 1 行分のデータ

' CSV のシートを選択
Dim ws As Worksheet
Set ws = ActiveSheet      ' 選択しているシート
'Set ws = Sheets(1)        ' 左から 1 番目のシート
'Set ws = Sheets("Sheet1") ' Sheet1 という名前のシート

' データの範囲を選択
Dim region As Range
Set region = ws.Range("A1").CurrentRegion ' セル「A1」を含むデータの範囲を取得
'Set region = ws.UsedRange                 ' シートで使用されている最大の範囲を取得

' 見出し行を取り除く
'Set region = region.Offset(1, 0)                           ' 範囲を 1 行下にずらす
'Set region = region.Resize(RowSize:=region.Rows.Count - 1) ' 範囲を 1 行分縮める

' 区切り文字の選択
Dim delimiter As String
delimiter = ","   ' カンマ区切り
'delimiter = vbTab ' タブ区切り
'delimiter = ";"   ' セミコロン区切り
'delimiter = " "   ' スペース区切り

Dim row As Range
For Each row In region.Rows ' 行のループ

    line = ""
    Dim cell As Range
    For Each cell In row.Columns ' 列のループ

        ' カンマ区切りで結合
        Dim item As Variant
        Select Case cell.Column
        Case 1 ' 文字列
            item = """" & cell.Value & """"

        Case 2 ' 数値
            item = Format(cell.Value, "0")

        Case 3 ' 日付
            item = Format(cell.Value, "yyyy/m/d")

        End Select

        If line = "" Then
            line = item
        Else
            line = line & delimiter & item
        End If

    Next

    ' 行を結合
    If csv = "" Then
        csv = line
    Else
        csv = csv & vbCrLf & line
    End If

Next

' 書き込み処理

著者：Tipsfound