この記事では、ExcelのマクロでAccessテーブルのデータをExcelファイルに出力・保存する方法についてご説明します。
【動画】ExcelのマクロでAccessテーブルのデータをExcelファイルに出力・保存する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
ExcelのマクロがAccessテーブルに対して条件に合致するデータをSELECT文を実行して抽出し、新規作成したExcelファイルのシートに抽出したデータを出力して保存しています。
マクロ作成の流れ
Excelファイルの例
今回は次のExcelファイルを用意しました。
「termVal」(A2のセル)には条件に指定する値(今回のサンプルは月)を入力し、「saveDataPath」(A5のセル)には取得したデータを出力したExcelファイルをどこに保存するかを指定します。
コードの例
Excelのマクロのコード(例)
Option Explicit
Sub test()
Dim DBName As String 'データベース名
Dim tblNM As String '取得元のテーブル名
Dim connDB As String 'データベース接続情報
Dim sqlStr As String 'SQL文
Dim cnt As Long 'カウンタ
Dim conn As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim newWB As Workbook '新規作成するExcelファイル用変数
Dim crntPath As String '本Excelファイルの格納先
Dim termVal As String '検索条件の値
Dim saveDataPath As String '抽出したデータの保存先
'抽出したデータの保存先を取得する
saveDataPath = Worksheets("work").Range("saveDataPath").Value
'検索条件の値を取得する
termVal = Worksheets("work").Range("termVal").Value
'カレントディレクトリのデータベースパスを取得
DBName = ThisWorkbook.Path & "\" & "0194.mdb"
'データを取得するテーブル名を取得
tblNM = "tbl_score_list"
'Connectionインスタンスの生成
Set conn = New ADODB.Connection
'データベース接続情報の取得
conn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" _
& DBName & ""
'Accessに接続する
conn.Open connDB
'Recordsetオブジェクトのインスタンスを生成する
Set rs = CreateObject("ADODB.Recordset")
'クライアントサイドカーソルに変更
rs.CursorLocation = 3
'データを取得するSELECT文を用意する
sqlStr = "select *"
sqlStr = sqlStr & " from "
sqlStr = sqlStr & " " & tblNM
sqlStr = sqlStr & " where"
sqlStr = sqlStr & " months"
sqlStr = sqlStr & " ="
sqlStr = sqlStr & " '" & termVal & "'"
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, conn, adOpenDynamic
If rs.RecordCount = 0 Then
'データ件数が0件の場合
MsgBox "データが存在しません"
'処理を終了する
Exit Sub
Else
'データが1件以上存在する場合
'取得したデータを貼り付けるための新たなExcelファイルを作成する
Set newWB = Workbooks.Add
'表の列数分だけ処理をループする
For cnt = 0 To rs.Fields.Count - 1
'表の列名をセルに出力する
newWB.Worksheets(1).Cells(1, cnt + 1).Value = rs.Fields.Item(cnt).Name
Next cnt
'先ほど貼り付けた列名の下の行にデータを貼り付ける
newWB.Worksheets(1).Cells(2, 1).Offset(0, 0).CopyFromRecordset rs
'新規作成したExcelファイルに名前を付けて保存する
Call newWB.SaveAs(saveDataPath & "\" & termVal & ".xlsx")
'新規作成したExcelファイルを閉じる
newWB.Close
End If
'各終了処理
conn.Close
'後処理
Set rs = Nothing
Set conn = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは18行目から27行目です。
'抽出したデータの保存先を取得する
saveDataPath = Worksheets("work").Range("saveDataPath").Value
'検索条件の値を取得する
termVal = Worksheets("work").Range("termVal").Value
'カレントディレクトリのデータベースパスを取得
DBName = ThisWorkbook.Path & "\" & "0194.mdb"
'データを取得するテーブル名を取得
tblNM = "tbl_score_list"
以上のコードは、Accessのデータベースの在り処やテーブル名、Excelファイルの保存先などを取得するコードです。
18行目は抽出したデータの保存先、21行目は検索条件の値、24行目はデータの取得先のAccessデータベースファイル、27行目はデータを抽出するAccessデータベースファイルのテーブル名です。
注目すべきコード②
次に見て頂きたいのは30行目から38行目です。
'Connectionインスタンスの生成
Set conn = New ADODB.Connection
'データベース接続情報の取得
conn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" _
& DBName & ""
'Accessに接続する
conn.Open connDB
以上のコードは、ExcelがAccessにADO接続する接続情報を取得してAccessに接続している処理のコードになります。
ADO接続するために必要なConnectionインスタンスを30行目で生成し、そのConnectionインスタンスに対してADO接続するために33行目から35行目で接続情報を設定します。
注目すべきコード③
次に見て頂きたいのは47行目から56行目です。
'データを取得するSELECT文を用意する
sqlStr = "select *"
sqlStr = sqlStr & " from "
sqlStr = sqlStr & " " & tblNM
sqlStr = sqlStr & " where"
sqlStr = sqlStr & " months"
sqlStr = sqlStr & " ="
sqlStr = sqlStr & " '" & termVal & "'"
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, conn, adOpenDynamic
以上のコードは、データを取得するSELECT文を用意して実行し、データを取得する処理のコードです。
47行目から53行目でデータを取得するSELECT文を用意し、56行目のOpenメソッドの引数にSELECT文を指定して実行することでデータを取得することができます。
取得したデータはRecordsetに格納されます。
注目すべきコード④
次に見て頂きたいのは58行目です。
If rs.RecordCount = 0 Then
以上のコードは、取得したデータの件数を判定しているIfを使ったコードです。
RecordCountプロパティの値はデータの件数ですが、この件数が0かそうでないかを確認しています。
注目すべきコード⑤
次に見て頂きたいのは72行目です。
'取得したデータを貼り付けるための新たなExcelファイルを作成する
Set newWB = Workbooks.Add
以上のコードは、新たにExcelファイルを開くコードです。
WorkbooksオブジェクトのAddメソッドを実行すると、新たにExcelファイルを開きます。
注目すべきコード⑥
次に見て頂きたいのは75行目から80行目です。
'表の列数分だけ処理をループする
For cnt = 0 To rs.Fields.Count - 1
'表の列名をセルに出力する
newWB.Worksheets(1).Cells(1, cnt + 1).Value = rs.Fields.Item(cnt).Name
Next cnt
以上のコードは、取得したデータをセルに出力する処理と、そのヘッダ(列名)をセルに出力する処理を行っているコードです。
75行目から80行目では、表の列の数だけ繰り返しヘッダ(列名)をセルに出力しています。
78行目のFields.Item.Nameプロパティからヘッダ(列名)を取得することができます。
ヘッダ(列名)をセルに出力し終わったら83行目で、ヘッダ(列名)の1つ下のセル位置に結合させたデータをセルに貼り付けています。
注目すべきコード⑦
次に見て頂きたいのは86行目から89行目です。
'新規作成したExcelファイルに名前を付けて保存する
Call newWB.SaveAs(saveDataPath & "\" & termVal & ".xlsx")
'新規作成したExcelファイルを閉じる
newWB.Close
以上のコードは、新規作成したExcelファイルに名前を付けて保存し、そのExcelファイルを閉じる処理のコードです。
86行目で新規作成したExcelファイルに名前を付けて保存しています。
なお、今回のサンプルでは、検索条件の値をファイル名に使っています。
例えば検索条件の値が「6月」の場合は、「6月.xlsx」とExcelファイルが作成されます。(Excelファイルの拡張子は「xlsx」を指定)
動作確認
マクロ実行前
今回は「Excelファイルの例」および「注目すべきコード⑦」のexcelファイルを使い、月が「6月」を条件に指定します。
また、今回扱うAccessのデータベースの「tbl_score_list」テーブルのデータは次の通りです。
以上のmonths列が「6月」のデータ(赤枠のデータ)を取得します。
マクロ実行後
マクロが実行されると、下の通りExcelファイルが生成されました。
ファイル名は「6月.xlsx」で、ファイルの中身を見てみるとAccessのデータベースの「tbl_score_list」テーブルにある「months」が6月のデータがシートに出力されていることが確認できます。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの10行目の「ADODB.Connection」と11行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim conn As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、ExcelのマクロでAccessテーブルのデータをExcelファイルに出力・保存する方法についてご説明しました。
AccessテーブルのデータをExcelファイルに出力・保存したい時は本記事を参考にしてみてくださいね。
Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら
Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。
Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。
→ 受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら
