この記事では、AccessのテーブルデータをCSVファイルに出力する方法についてご説明します。
【動画】AccessのテーブルデータをCSVファイルに出力する実際の動き
本題に入る前に、まずは次のツイートをご覧ください。
AccessのテーブルデータをCSVファイルに書き出す処理を動画に撮ってみました😊
CSVファイルにAccessのテーブルデータを書き込む際、データを1件ずつCSVファイルに書き込むのではなく、全データを一つに結合させてから、その結合データをCSVファイルに出力させています😃#ExcelVBA#Access pic.twitter.com/L6q9wWciJ9
— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) August 24, 2021
AccessのテーブルデータをExcelのマクロが取得してCSVファイルに出力しています。
AccessのテーブルデータをCSVファイルに出力する方法
AccessのテーブルデータをCSVファイルに出力するには、次の流れの通りにコードを書いていきます。
Excelのマクロ
このインスタンスは、Accessのテーブルデータの参照・挿入に必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
この列名はAccessのテーブルデータを取得するSELECT文で使います。
(SELECT文に直接列名を指定したり、列名は使わずに「SELECT *」でテーブルデータを取得する場合は、本STEPの処理は必要ありません。)
テーブルデータの文字列を全て一つの文字列に結合させるイメージです。
一つに結合させる理由は、一つの文字列にすることでCSVファイルの書き込みが1回で済むからです。(何度も書き込まないようにする)
※何度も書き込む方式でも間違いではありません。今回は1回の書き込み方式で説明します。
コードの例
Excelのマクロのコード(例)
Dim DbName As String 'データベース名
Dim tblNM As String '取得元のテーブル名
Dim outputFile As String 'Accessから取得したテーブルデータを書き出すファイル
Dim connDB As String 'データベース接続情報
Dim sqlStr As String 'SQL文
Dim cnt As Long 'カウンタ
Dim itm As Variant '配列から取得した値を格納する変数
Dim dataStr As String 'Accessから取得したテーブルデータ格納用変数
Dim getVal() As Variant '取得したデータ格納用並列
Dim adoCON As New ADODB.Connection 'Connection用変数
Dim adoRS As ADODB.Recordset 'レコードセット用変数
Dim FSO As Object 'FileSystemObjectのインスタンス用変数
'カレントディレクトリのデータベースパスを取得
DbName = ActiveWorkbook.Path & "\" & "0064.mdb"
'データを取得するテーブル名を取得
tblNM = "tbl_info_list"
'Accessから取得したテーブルデータを書き出すファイル
outputFile = ActiveWorkbook.Path & "\" & "0064.csv"
'データベース接続情報の取得
adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" _
& DbName & ""
'Accessに接続する
adoCON.Open connDB
'Recordsetオブジェクトのインスタンスを生成する
Set adoRS = CreateObject("ADODB.Recordset")
'クライアントサイドカーソルに変更
adoRS.CursorLocation = 3
'先ほど取得したデータベース接続情報が設定されているadoCONをActiveConnectionプロパティに設定し、
'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
'⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
adoRS.ActiveConnection = adoCON
'カーソルを開く
adoRS.Open tblNM, adoCON
'Accessからテーブルデータを取得するSQL文を作成
sqlStr = "SELECT "
For cnt = 0 To adoRS.Fields.Count - 1
'列名をSELECT文に使う
sqlStr = sqlStr & adoRS.Fields.Item(cnt).Name & ","
Next
sqlStr = Left(sqlStr, Len(sqlStr) - 1) '末尾の「'」を削除
sqlStr = sqlStr & " FROM " & tblNM
sqlStr = sqlStr & " ORDER BY 1 ASC"
'レコードセットを閉じる
adoRS.Close
'Accessからテーブルデータを取得する
adoRS.Open sqlStr, adoCON, adOpenDynamic
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = adoRS.GetRows
'レコードセットを閉じる
adoRS.Close
'カウンタを初期化する
cnt = 1
'Accessから取得したテーブルデータをdataStrに書き込んでいく
For Each itm In getVal()
If cnt > 1 And (cnt Mod 2) = 0 Then
dataStr = dataStr & itm & vbCrLf
Else
dataStr = dataStr & itm & ","
End If
'カウントを増やす
cnt = cnt + 1
Next
'FileSystemObjectのインスタンス化
Set FSO = CreateObject("Scripting.FileSystemObject")
With FSO.CreateTextFile(outputFile)
.Write dataStr 'Accessから取得したテーブルデータをCSVファイルに書き出す
.Close '閉じる
End With
'各終了処理
Set FSO = Nothing
adoCON.Close
If Not adoRS Is Nothing Then Set adoRS = Nothing
If Not adoCON Is Nothing Then Set adoCON = Nothing
コードの解説
注目すべきコード①
最初に見て頂きたいのは84行目から89行目です。
'FileSystemObjectのインスタンス化
Set FSO = CreateObject("Scripting.FileSystemObject")
With FSO.CreateTextFile(outputFile)
.Write dataStr 'Accessから取得したテーブルデータをCSVファイルに書き出す
.Close '閉じる
End With
87行目のwriteメソッドでAccessから取得したテーブルデータをCSVファイルに書き出しています。
CSVファイルにテーブルデータを書き出したら88行目のcloseメソッドで開いたCSVファイルを閉じます。(保存も完了)
注目すべきコード②
次に見て頂きたいのは71行目から81行目です。
'Accessから取得したテーブルデータをdataStrに書き込んでいく
For Each itm In getVal()
If cnt > 1 And (cnt Mod 2) = 0 Then
dataStr = dataStr & itm & vbCrLf
Else
dataStr = dataStr & itm & ","
End If
'カウントを増やす
cnt = cnt + 1
Next
73行目または75行目でAccessから取得したテーブルデータが入った変数itmの値を結合し、変数dataStrに代入していきます。
73行目は改行文字を文字列の末尾に、75行目はカンマ「,」を文字列の末尾に結合させています。
実際にCSVファイルに出力されるデータ(下のデータの並びを参考に)を見て頂きたいのですが、73行目は1列目のデータの後にカンマ「,」を付け(ex.「data0000001,」)、75行目は1列目と2列目のデータを結合した後のデータ(ex.「data0000001,description0000001」)の後に改行文字「vbCrLf」を結合させてデータを改行させています。
data0000001,description0000001 data0000002,description0000002 data0000003,description0000003 data0000004,description0000004 data0000005,description0000005 data0000006,description0000006 data0000007,description0000007 data0000008,description0000008 data0000009,description0000009 data0000010,description0000010 data0000011,description0000011 data0000012,description0000012
注目すべきコード③
次に見て頂きたいのは46行目から62行目です。
'Accessからテーブルデータを取得するSQL文を作成
sqlStr = "SELECT "
For cnt = 0 To adoRS.Fields.Count - 1
'列名をSELECT文に使う
sqlStr = sqlStr & adoRS.Fields.Item(cnt).Name & ","
Next
sqlStr = Left(sqlStr, Len(sqlStr) - 1) '末尾の「'」を削除
sqlStr = sqlStr & " FROM " & tblNM
sqlStr = sqlStr & " ORDER BY 1 ASC"
'レコードセットを閉じる
adoRS.Close
'Accessからテーブルデータを取得する
adoRS.Open sqlStr, adoCON, adOpenDynamic
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = adoRS.GetRows
46行目から53行目でAccessのテーブルデータを取得するSELECT文を用意し、62行目でrecordsetのGetRowsメソッドでAccessからテーブルデータを取得しています。
注目すべきコード④
次に見て頂きたいのは29行目です。
'Accessに接続する
adoCON.Open connDB
ConnectionオブジェクトのOpenメソッドを使ってAccessに接続しています。
補足
本記事では、取得するAccessテーブルデータのテーブルの列名を取得しています。
列名は以下のコード実行し、
'カーソルを開く
adoRS.Open tblNM, adoCON
以下のコードで列名が取得できます。(nameプロパティに列名が格納されています)
adoRS.Fields.Item(cnt).Name
ウォッチウィンドウで確認すると確かに列名が確認できます。
Itemプロパティの0番目が先頭で、順番にnameプロパティから列名を取得していきます。
なお、この列名はAccessのデータを取得するSELECT文で使います。
ただし、SELECT文に直接列名を指定したり、列名は使わずに「SELECT *」でテーブルデータを取得する場合は、列名の取得は不要です。
ちなみに上記SELECT文で参照するテーブルの定義は次の画像の通りです。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの10行目の「ADODB.Connection」と11行目の「ADODB.Recordset」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。
Dim adoCON As New ADODB.Connection 'Connection用変数
Dim adoRS As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、ExcelのマクロからAccessのテーブルデータをCSVファイルに出力する方法についてご説明しました。
ザックリとした処理の流れとしては、
- ①Accessに接続
- ②CSVファイルに書き出したいテーブルデータを抽出するSQL文を記述
- ③ ②のSQL文の条件を満たしたテーブルデータを抽出し、その抽出データをレコードセットから取得
- ④ ③のデータの文字列を全て結合させて変数に格納
- ⑤ ④の変数の値をCSVファイルに書き込む
以上になります。
AccessのテーブルデータをCSVファイルに出力したい時に本記事を参考にしていただけたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
