更新日:、 作成日:
VBA CSV ファイルの書き込みと保存
はじめに
Excel VBA マクロで CSV ファイルの書き込みと保存する方法を紹介します。
文字列をダブルクォーテーションで囲んだり、数値や日付の形式を指定できます。
区切り文字の指定や、文字コードを Shift_JIS や UTF-8 で保存できます。
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
' 書き込み処理