この記事では、ExcelのマクロからAccessのクエリを実行してデータを取得する方法についてご説明します。
なお、取得するデータのヘッダも欲しい場合は次の記事をご覧ください。
【ExcelVBA】ExcelのマクロからAccessのクエリを実行してデータを取得する方法(ヘッダあり)
【動画】ExcelのマクロからAccessのクエリを実行してデータを取得する実際の動き(ヘッダ無し)
本題に入る前に、まずは次の動画をご覧ください。
ExcelのマクロがAccessのデータベースファイルを開き、recordsetを使ってクエリを実行しデータを取得しています。
取得後はCopyFromRecordsetメソッドにrecordsetを引数指定して実行し、取得したデータをExcelのシートに出力しています。
マクロ作成の流れ
このConnectionオブジェクトのインスタンスが生成されていないとマクロがAccessにアクセスすることができないので必ず生成しておきます。
Accessのデータベース接続情報は、Accessのデータベースのファイルの置き場所やAccessのデータベースのファイル名などの情報です。
STEP.2で取得したAccessのデータベース接続情報をOpenメソッドの引数に指定し実行することで、ExcelのマクロがAccessのデータベースに接続しクエリを参照したり実行できるようになります。
Recordsetオブジェクトのインスタンスを生成することで、ExcelのマクロがRecordsetを扱うことができるようになります。
コードの例
Excelのマクロのコード(例)
Option Explicit
Sub test()
Dim accFileNM As String 'Accessのデータベースファイル
Dim cn As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
'カレントディレクトリのデータベースパスを取得
accFileNM = ActiveWorkbook.Path & "\" & "0174.mdb"
'Connectionオブジェクトのインスタンスを生成する
Set cn = New ADODB.Connection
'データベース接続情報の取得
cn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" _
& accFileNM & ""
'Accessのデータベースファイルに接続する
cn.Open
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'指定したクエリを開く
rs.Open Worksheets("work").Range("qName").Value, cn
'レコード有無判定(EOFプロパティがTrueならデータなし、Falseならデータあり)
If rs.EOF = False Then
'データが存在する場合
'クエリを実行して取得したデータをセルに出力する
Worksheets("data").Cells(1, 1).CopyFromRecordset rs
End If
'Connectionを閉じる
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは13行目から21行目です。
'Connectionオブジェクトのインスタンスを生成する
Set cn = New ADODB.Connection
'データベース接続情報の取得
cn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" _
& accFileNM & ""
'Accessのデータベースファイルに接続する
cn.Open
以上のコードはExcelのマクロがAccessのデータベースファイルに接続・オープンする処理のコードです。
13行目でConnectionオブジェクトのインスタンスを生成し、16行目でConnectionオブジェクトのConnectionStringプロパティにデータベース接続情報を格納しています。
なお、18行目でAccessのデータベースファイル名をフルパスで指定しています。
ちなみに変数accFileNMは、10行目でAccessのデータベースファイル名をフルパスで取得しています。
'カレントディレクトリのデータベースパスを取得
accFileNM = ActiveWorkbook.Path & "\" & "0174.mdb"
21行目では、16行目で取得したデータベース接続情報を元にOpenメソッドを実行してAccessのデータベースファイルを開きます。
注目すべきコード②
次に見て頂きたいのは24行目から27行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'指定したクエリを開く
rs.Open Worksheets("work").Range("qName").Value, cn
以上のコードはExcelのマクロがクエリを開く処理のコードです。
24行目でRecordsetオブジェクトのインスタンスを生成し、27行目でRecordsetオブジェクトのOpenメソッドの引数にクエリ名とConnectionオブジェクトのインスタンスを指定して実行します。
今回サンプルで用意した下のExcelのシート「work」のセル「qName」に入力されたクエリを呼び出したいので、Openメソッドにそのセル「qName」のクエリ名を引数に指定しています。
実行後は、クエリ実行で取得されたデータがRecordsetに格納されます。
注目すべきコード③
次に見て頂きたいのは30行目から37行目です。
'レコード有無判定(EOFプロパティがTrueならデータなし、Falseならデータあり)
If rs.EOF = False Then
'データが存在する場合
'クエリを実行して取得したデータをセルに出力する
Worksheets("data").Cells(1, 1).CopyFromRecordset rs
End If
以上のコードは、取得したデータが1件以上なのか、それとも0件なのかを判定し、1件以上取得されている場合はExcelのシートに貼り付ける処理のコードです。
30行目で取得したデータが1件以上なのか、それとも0件なのかを判定しています。
データが1件以上の場合は、35行目でCopyFromRecordsetのメソッドにRecordsetインスタンスを引数指定して実行しExcelのシートにそのデータを貼り付けています。
動作確認
マクロ実行前
今回用意したExcelファイルは以下で、セル「aName」に「Q_都道府県取得クエリ」というクエリ名が入力されています。
Accessにあるこの「Q_都道府県取得クエリ」をマクロが呼び出して取得したデータをExcelのシートに出力します。
マクロ実行後
マクロを実行すると、ExcelのシートにAccessのクエリ「Q_都道府県取得クエリ」の実行結果が出力されました。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの6行目の「ADODB.Connection」と7行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cn 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マスター講座はこちら
