VBAを使って実装する処理のなかでは頻繁に使われる「CSV出力」処理ですが、主な実装方法としては以下が定番です。
- Openステートメントを使用したCSV出力
- FileSystemObjectを使用したCSV出力
- Stream(ADO)オブジェクトを使用したCSV出力
今回の記事では、レコードセットを出力するケースを対象に、「VBAでCSVデータを出力する処理」の代表的な実装方法を紹介していきます。
また、使用するオブジェクトやメソッドに関しては、Microsoftの公式ドキュメントのリンクを貼っておきます。
詳しい説明を確認したい場合や、対象のオブジェクトで他のメソッドも確認したい場合は、そのリンクから対象のページや関連するページの探索をしてみてください。
CSV出力はVBAの実務上必須の処理です！
ネットで拾ったサンプルコードを何となく使用するのではなく、個々の違いを理解して最適な実装を選択できるようになりましょう！
サンプルプログラムの説明
今回紹介するサンプルプログラムは、すべて「Subプロシージャ」で作成しております。
また、そのSubプロシージャでは引数としてRecordsetオブジェクトを受け取り、Subプロシージャ内でCSVを出力する実装にしてあります。
プログラムをシンプルにするために、エラー処理は入れていません。
実際の業務で使用する場合は、エラー処理を入れつつ、処理結果も呼び出し元に返せるように、Functionプロシージャで作成することを推奨します。
尚、当記事のサンプルプログラムでは、「レコードセットの行ごとにループして一件ずつ出力する実装」と、「RecordsetオブジェクトのGetStringメソッドを使用してレコードセットを丸っと全行出力する実装」の2パターンを掲載します。
こちらも利用環境に合わせて使い分けてください。
当記事ではDo Untilでループしています。
行単位でループしているので、行の値によって処理を分岐したり、データの値を加工する場合はこちらの方法で実装する必要があります。
RecordsetオブジェクトのGetStringメソッドを呼んで出力する場合はループする必要が無いため、プログラムをシンプルに記述できます。
ただ、当然行ごとの処理の分岐はできません。
また、処理速度は直観的にこちらの処理の方が速そうだと思うのですが、実際にはループさせた方が圧倒的に速かったです。
これは、GetStringメソッド内で区切り文字を入れたりといった変換、加工処理が内部的には高負荷なんだと思われます。
■構文
Variant = recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)
引数の説明、GetStringメソッドの詳しい内容はMicrosoftのドキュメントを参照してください。
「Openステートメント」を使用したCSV出力サンプル
VBAのCSV出力処理の実装方法で一番シンプルなのは「Openステートメント」を使用した処理です。
Openステートメントや関連するステートメントの詳しい解説は、以下のMicrosoftの公式ドキュメントをご確認ください。
「Openステートメント」レコードセットをループしてCSVを出力
Sub test1(rs As Recordset)
Dim FileFullPath As String
'出力先ファイルのフルパスを指定します。
FileFullPath = "C:\test\test1.csv"
'Openステートメントを追記(Append)モードで実行します。
'追記が不要ならFor Outputを指定します。
Open FileFullPath For Append As #1
'レコードセットの行数分ループしてテキストファイルに書き込みます。
Do Until rs.EOF
Print #1, rs(0).Value & "," & rs(1).Value & _
"," & rs(2).Value & "," & rs(3).Value & "," & rs(4).Value
rs.MoveNext
Loop
'テキストファイルを閉じます。
Close #1
End Sub
「Openステートメント」GetStringメソッドでまるっとCSVを出力
Sub test2(rs As Recordset)
Dim FileFullPath As String
'出力先ファイルのフルパスを指定します。
FileFullPath = "C:\test\test2.csv"
'Openステートメントを追記(Append)モードで実行します。
'追記が不要ならFor Outputを指定します。
Open FileFullPath For Append As #1
'レコードセットの全行をカンマ区切りで一気に書き込みます。
Print #1, rs.GetString(adClipString, , ",", vbCrLf)
'テキストファイルを閉じます。
Close #1
End Sub
「FileSystemObjectオブジェクト」を使用したCSV出力サンプル
VBAでファイル操作をする場合に必須の「FileSystemObject」を使用した場合のCSV出力処理です。
「FileSystemObject」はVBAで外部ファイルやフォルダを扱う場合に必ず使用するオブジェクトです。
よって、前後の処理でFileSystemObjectを使用していた場合は、ついでにFileSystemObjectを使用してCSV出力も実装するような使い分けでも良いかと思います。
FileSystemObjectや関連するメソッドの詳しい解説は、以下のMicrosoftの公式ドキュメントをご確認ください。
OpenTextFile メソッド
WriteLine メソッド
「FileSystemObject」レコードセットをループしてCSVを出力
Sub Test3(rs As Recordset)
Dim objFS As Object
Dim objOutputFile As Object
Dim FileFullPath As String
'出力先ファイルのフルパスを指定します。
FileFullPath = "C:\test\test3.csv"
'ファイルシステムオブジェクトを生成します。
Set objFS = CreateObject("Scripting.FileSystemObject")
'出力先ファイルを追記モードで開きます。
'※存在しなければ作成します。追記不要であれば第二引数：8→2
Set objOutputFile = objFS.OpenTextFile(FileFullPath, 8, True)
'レコードセットの全行をカンマ区切りで一気に書き込みます。
objOutputFile.WriteLine rs.GetString(adClipString, , ",", vbCrLf)
'開いているファイルを閉じます。
objOutputFile.Close
'オブジェクトを破棄します。
Set objOutputFile = Nothing
Set objFS = Nothing
End Sub
「FileSystemObject」GetStringメソッドでまるっとCSVを出力
Sub test4(rs As Recordset)
Dim objFS As Object
Dim objOutputFile As Object
Dim FileFullPath As String
'出力先ファイルのフルパスを指定します。
FileFullPath = "C:\test\test4.csv"
'ファイルシステムオブジェクトを生成します。
Set objFS = CreateObject("Scripting.FileSystemObject")
'出力先ファイルを追記モードで開きます。
'※存在しなければ作成します。追記不要であれば第二引数：8→2
Set objOutputFile = objFS.OpenTextFile(FileFullPath, 8, True)
'レコードセットの行数分ループしてテキストファイルに書き込みます。
Do Until rs.EOF
objOutputFile.WriteLine rs(0).Value & "," & rs(1).Value & _
"," & rs(2).Value & "," & rs(3).Value & "," & rs(4).Value
rs.MoveNext
Loop
'開いているファイルを閉じます。
objOutputFile.Close
'オブジェクトを破棄します。
Set objOutputFile = Nothing
Set objFS = Nothing
End Sub
「Stream(ADO)オブジェクト」を使用したCSV出力サンプル
ADOの「Streamオブジェクト」を使用したCSV出力処理のサンプルです。
当サンプルコードでは、出力したテキストファイルの文字コードに「UTF-8」が使われるようにしてあります。
出力先のテキストファイルの文字コードの指定も併せて実施する必要がある場合は、「Streamオブジェクト」を使用するのが良いでしょう。
また、Streamオブジェクトでは、テキスト形式のデータだけではなくバイナリへ変換して扱えるので、CSV出力用途よりバイナリで扱う場合に重宝するオブジェクトです。
Streamオブジェクトや関連するメソッドの詳しい解説は、以下のMicrosoftの公式ドキュメントをご確認ください。
「Streamオブジェクト」レコードセットをループしてCSVを出力
Sub test5(rs As Recordset)
Dim objStrm As Object
Dim FileFullPath As String
'出力先ファイルのフルパスを指定します。
FileFullPath = "C:\test\test5.csv"
'Streamオブジェクトを生成します。
Set objStrm = CreateObject("ADODB.Stream")
'Typeを指定します。1：バイナリ、2：テキスト
objStrm.Type = 2
'文字コードを指定します。
objStrm.Charset = "UTF-8"
'Streamオブジェクトを開き操作可能にします。
objStrm.Open
Do Until rs.EOF
'末尾の引数に1を指定しないと改行が入らない
objStrm.WriteText rs(0).Value & "," & rs(1).Value & _
"," & rs(2).Value & "," & rs(3).Value & "," & rs(4).Value, 1
rs.MoveNext
Loop
'出力先ファイルに保存します。※同じファイル名があれば上書き
objStrm.SaveToFile FileFullPath, 1
objStrm.Close
Set objStrm = Nothing
End Sub
「Streamオブジェクト」GetStringメソッドでまるっとCSVを出力
Sub test6(rs As Recordset)
Dim objStrm As Object
Dim FileFullPath As String
'出力先ファイルのフルパスを指定します。
FileFullPath = "C:\test\test6.csv"
'Streamオブジェクトを生成します。
Set objStrm = CreateObject("ADODB.Stream")
'Typeを指定します。1：バイナリ、2：テキスト
objStrm.Type = 2
'文字コードを指定します。
objStrm.Charset = "UTF-8"
'Streamオブジェクトを開き操作可能にします。
objStrm.Open
'レコードセットの全行をカンマ区切りで一気に書き込みます。
objStrm.WriteText rs.GetString(adClipString, , ",", vbCrLf)
'出力先ファイルに保存します。※同じファイル名があれば上書き
objStrm.SaveToFile FileFullPath, 1
objStrm.Close
Set objStrm = Nothing
End Sub
最後に(処理速度の違いなど補足)
今回は、VBAでCSVデータを出力する場合の代表的な実装例を紹介しました。
注意が必要なのは、「GetStringメソッド」を使用して出力する場合は、かなり処理が遅いです。
当環境では、数千行のレコードセットを処理するだけでしたが、ループ処理と比較して、GetStringメソッドの方が数秒遅く、データ件数の割にはかなり大きな違いになりました。
よって、大量のデータをレコードセットで扱う場合は、GetStringメソッドの使用は避けた方が良さそうです。
また、テキストファイルを生成して書き込むための、「Openステートメント」、「FileSystemObject」、「Streamオブジェクト」については、処理時間を測ってみましたが、数千件程度の行数ではそれほど処理時間に大きな違いはありませんでした。
VBAではCSVを扱う機会も多く、データベースから必要なデータを取得してCSVに出力して、他システムに取り込まるなどの処理はどこの企業でも行われていたりします。
普段何気なく実装しているCSV出力処理ですが、複数ある実装方法を把握し、その都度最適な実装方法を選択できるのが望ましいかと思います。
今回の記事が誰かの助けになれば幸いです。
今回も読んでいただきましてありがとうございました。
また次回もよろしくお願いいたします。
