【ExcelVBA】ExcelのマクロからAccessのクエリを実行してデータを取得する方法(ヘッダあり)

6分

SHARE

この記事では、ExcelのマクロからAccessのクエリを実行してヘッダ付きでデータを取得する方法についてご説明します。

なお、取得するデータのヘッダが不要の場合は次の記事をご覧ください。

【ExcelVBA】ExcelのマクロからAccessのクエリを実行してデータを取得する方法(ヘッダ無し)

すぐにソースコードを見たい場合はこちらをクリック

【動画】ExcelのマクロからAccessのクエリを実行してデータを取得する実際の動き(ヘッダあり)

本題に入る前に、まずは次の動画をご覧ください。


ExcelのマクロがAccessのデータベースファイルを開き、recordsetを使ってクエリを実行しデータを取得しています。

取得後はまずRecordsetからヘッダの文言を取り出してシートに出力しています。

その後にCopyFromRecordsetメソッドにrecordsetを引数指定して実行し、取得したデータをExcelのシートに出力しています。

目次へ戻る

マクロ作成の流れ

STEP.1
Connectionオブジェクトのインスタンスを生成する
Connectionオブジェクトのインスタンスを生成します。
このConnectionオブジェクトのインスタンスが生成されていないとマクロがAccessにアクセスすることができないので必ず生成しておきます。
STEP.2
Accessのデータベース接続情報を取得する
Accessのデータベース接続情報を取得します。
Accessのデータベース接続情報は、Accessのデータベースのファイルの置き場所やAccessのデータベースのファイル名などの情報です。
STEP.3
Accessのデータベースファイルに接続する
Accessのデータベースファイルに接続します。
STEP.2で取得したAccessのデータベース接続情報をOpenメソッドに指定し実行することでExcelのマクロがAccessのデータベースに接続し、クエリを参照したり実行できるようになります。
STEP.4
Recordsetオブジェクトのインスタンスを生成する
Recordsetオブジェクトのインスタンスを生成します。
Recordsetオブジェクトのインスタンスを生成することで、ExcelのマクロがRecordsetを扱うことができるようになります。
STEP.5
recordsetオブジェクトのOpenメソッドを実行してクエリを開く
recordsetオブジェクトのOpenメソッドを実行してクエリを開きます。
STEP.6
Recordsetからヘッダを取得してシートに出力する
Recordsetからヘッダを取得してシートに出力します。
STEP.7
CopyFromRecordsetメソッドにrecordsetを引数指定して実行し、STEP.5でクエリが取得したデータをデータをExcelのシートに出力する
CopyFromRecordsetメソッドにrecordsetを引数指定して実行し、STEP.5でクエリが取得したデータをExcelのシートに出力します。
目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Sub test()

    Dim accFileNM   As String               'Accessのデータベースファイル
    Dim cn          As ADODB.Connection     'Connection用変数
    Dim rs          As ADODB.Recordset      'レコードセット用変数
    Dim hdrStr      As Variant              'ヘッダーの値
    Dim cnt         As Integer              'カウンタ用変数
                
    'カレントディレクトリのデータベースパスを取得
    accFileNM = ActiveWorkbook.Path & "\" & "0175.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
    
        'データが存在する場合
        
        'ヘッダの数だけループさせる
        For cnt = 0 To rs.Fields.Count - 1
            
            '取得したヘッダをシートに出力する
            Worksheets("data").Cells(1, cnt + 1).Value = rs.Fields.Item(cnt).Name

        Next
        
        'クエリを実行して取得したデータをセルに出力する
        Worksheets("data").Cells(2, 1).CopyFromRecordset rs
        
    End If
    
    'Connectionを閉じる
    cn.Close
    
    Set cn = Nothing
    Set rs = Nothing
    
 End Sub

注目すべきコード①

最初に見て頂きたいのは15行目から23行目です。

    'Connectionオブジェクトのインスタンスを生成する
    Set cn = New ADODB.Connection
    
    'データベース接続情報の取得
    cn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & accFileNM & ""
          
    'Accessのデータベースファイルに接続する
    cn.Open

以上のコードはExcelのマクロがAccessのデータベースファイルに接続・オープンする処理のコードです。

15行目でConnectionオブジェクトのインスタンスを生成し、18行目でConnectionオブジェクトのConnectionStringプロパティにデータベース接続情報を格納しています。

なお、20行目でAccessのデータベースファイル名をフルパスで指定しています。

ちなみに変数accFileNMは、12行目でAccessのデータベースファイル名をフルパスで取得しています。

    'カレントディレクトリのデータベースパスを取得
    accFileNM = ActiveWorkbook.Path & "\" & "0175.mdb"

23行目では、18行目で取得したデータベース接続情報を元にExcelのマクロがOpenメソッドを実行してAccessのデータベースファイルを開きます。

注目すべきコード②

最初に見て頂きたいのは26行目から29行目です。

    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = New ADODB.Recordset
    
    '指定したクエリを開く
    rs.Open Worksheets("work").Range("qName").Value, cn

以上のコードはExcelのマクロがクエリを開く処理のコードです。

26行目でRecordsetオブジェクトのインスタンスを生成し、29行目でRecordsetオブジェクトのOpenメソッドの引数にクエリ名とConnectionオブジェクトのインスタンスを指定して実行します。

今回サンプルで用意した下のExcelのシート「work」のセル「qName」に入力されたクエリを呼び出したいので、Openメソッドにそのセル「qName」のクエリ名を引数に指定しています。

実行後は、クエリ実行で取得されたデータがRecordsetに格納されます。

注目すべきコード④

次に見て頂きたいのは32行目から47行目です。

    'レコード有無判定(EOFプロパティがTrueならデータなし、Falseならデータあり)
    If rs.EOF = False Then
    
        'データが存在する場合
        
        'ヘッダの数だけループさせる
        For cnt = 0 To rs.Fields.Count - 1
            
            '取得したヘッダをシートに出力する
            Worksheets("data").Cells(1, cnt + 1).Value = rs.Fields.Item(cnt).Name
        Next
        
        'クエリを実行して取得したデータをセルに出力する
        Worksheets("data").Cells(2, 1).CopyFromRecordset rs
        
    End If

以上のコードは、取得したデータが1件以上なのか、それとも0件なのかを判定し、1件以上取得されている場合はヘッダと、取得したデータをExcelのシートに貼り付ける処理のコードです。

取得したデータが1件以上なのか、それとも0件なのかを32行目で判定しています。

データが1件以上の場合は、40行目で取得したヘッダをExcelのシート「data」に出力しています。

さらに、45行目でCopyFromRecordsetのメソッドにRecordsetインスタンスを引数指定して実行しExcelのシートにそのデータを貼り付けています。

目次へ戻る

動作確認

マクロ実行前

今回用意したExcelファイルは以下で、セル「aName」に「Q_都道府県取得クエリ」というクエリ名が入力されています。

Accessにあるこの「Q_都道府県取得クエリ」をマクロが呼び出して取得したデータをExcelのシートに出力します。

マクロ実行後

マクロを実行すると、ExcelのシートにAccessのクエリ「Q_都道府県取得クエリ」の実行結果が出力されました。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. 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マスター講座はこちら



目次へ戻る

前の記事

【AccessVBA】AccessのマクロからExcelのマク…

次の記事

【ExcelVBA】数式自体を文字列検索してどのセルで使われて…