サイトについて      連絡先   検索

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").CurrentRegionws.UsedRangeのようにして、そのシートの範囲を取得できます。


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

取得したデータの範囲に見出し行が含まれているときはregion.Offsetregion.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

' 書き込み処理

関連ページ