【EXCEL：VBA】ACCESSからクエリを指定してデータを取得したい

この記事では、指定したACCESSファイルからクエリを指定してデータを取得する方法について説明しています。

ACCESSファイルを開いて、クエリを指定して、データを取得し、EXCELファイルにコピーするのは大変な作業です。

今回ご紹介する機能を利用すれば、ACCESSファイルを開かずに欲しいデータを一瞬で取得することができます。

EXCELシート上に取得したデータが残るため、表に加工したり、ピポットテーブルの元データにしたり、2次加工も思いのままにできるようになります。

それでは、指定したACCESSからクエリを指定してデータを取得する機能を作成していきましょう。

困っている女性

ACCESSに入っている各種データを作業でよく利用することがあるの。

困っている女性

ACCESSファイルをいちいち開いて、条件を指定してデータを絞ってデータを表示させ、そのデータをコピーして、EXCELに貼り付ける作業が地味に面倒なの。

困っている女性

EXCELから条件をしてすれば、直接データを取得できると便利なんだけどなあ。

それなら、VBAを使えばボタン1つ押すだけで、ACCESSから欲しいデータをEXCELに取得することができるよ。

解決する男性

しかも、取得したデータはEXCEL上に残るので好きなように加工もEXCELできるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

「機能概要図」（ACCESSからクエリを指定してデータを取得する）

今回作成する機能の概要図は、以下です。

「機能概要図」（ACCESSからクエリを指定してデータを取得する）

指定したACCESSファイルから、クエリを指定してデータを取得する機能を、1つ1つ順番に説明していきます。

ACCESSファイルパス指定欄、クエリ指定欄、取得したデータの表示欄を準備する

まず、ACCESSファイルとクエリを指定する欄を作成します。

そのあと、指定したクエリデータを表示するシートを作成します。

「メイン」シートを作成する

メインシートを作成する

「メイン」という名前のシートを作成します。
A1セルに「ACCESSファイルパス」のタイトル欄を作成します。
A2セルに「クエリ」のタイトル欄を作成します。
B1とB2セルに、「ACCESSファイルパス」と「クエリ」を指定する欄を作成します。

B2セルは、クエリを入力するので少し高さと幅を持たせましょう。

「データ」シートを作成する

データ表示するシートを作成する

「データ」という名前のシートを作成します。
あとは、特になにもしなくてよいです。

1行目には、取得したクエリデータの項目名が表示されます。

2行目以降には、クエリデータが表示されます。

VBAでACCESSからクエリを指定してデータを取得する機能を作成する

開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。

Microsoft Visual Basic for Applicationsを起動する

「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。

VBAプログラムを入力する

標準モジュールを追加して、以下のソースを書きます。

Public Sub GetData()
    Dim shtMain As Worksheet
    Dim shtData As Worksheet
    Dim con As Object
    Dim rs As Object
    Dim i As Integer
    
    '①メインシートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    
    '②データシートを変数に格納する
    Set shtData = ThisWorkbook.Sheets("データ")
    
    '③データシートをクリアする
    shtData.Cells.ClearContents
    
    '④ADODBコネクションオブジェクトを生成する
    Set con = CreateObject("ADODB.Connection")
    
    '⑤指定されているACCESSに接続する
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & shtMain.Range("B1")
            
    '⑥指定されているクエリでACCESSからデータを取得する
    Set rs = con.Execute(shtMain.Range("B2"))
    
    '⑦取得したデータのフィールド名をデータシートにセットする
    For i = 0 To rs.Fields.Count - 1
        shtData.Cells(1, i + 1) = rs.Fields(i).Name
    Next
    
    '⑧取得したデータをデータシートに貼り付けする
    shtData.Range("A2").CopyFromRecordset rs

    '⑨使用したオブジェクトを閉じる
    rs.Close
    con.Close
    
    '⑩使用したオブジェクトをメモリから開放する
    Set rs = Nothing
    Set con = Nothing
    
    MsgBox "完了"
    
End Sub

Public Sub GetData()

Dim shtMain As Worksheet

Dim shtData As Worksheet

Dim con As Object

Dim rs As Object

Dim i As Integer

'①メインシートを変数に格納する

Set shtMain = ThisWorkbook.Sheets("メイン")

'②データシートを変数に格納する

Set shtData = ThisWorkbook.Sheets("データ")

'③データシートをクリアする

shtData.Cells.ClearContents

'④ADODBコネクションオブジェクトを生成する

Set con = CreateObject("ADODB.Connection")

'⑤指定されているACCESSに接続する

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & shtMain.Range("B1")

'⑥指定されているクエリでACCESSからデータを取得する

Set rs = con.Execute(shtMain.Range("B2"))

'⑦取得したデータのフィールド名をデータシートにセットする

For i = 0 To rs.Fields.Count - 1

shtData.Cells(1, i + 1) = rs.Fields(i).Name

'⑧取得したデータをデータシートに貼り付けする

shtData.Range("A2").CopyFromRecordset rs

'⑨使用したオブジェクトを閉じる

rs.Close

con.Close

'⑩使用したオブジェクトをメモリから開放する

Set rs = Nothing

Set con = Nothing

MsgBox "完了"

End Sub

次に、EXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。

ACCESSからクエリを指定してデータを取得する機能の使い方

それでは、ACCESSからクエリを指定してデータを取得する方法を説明します。

まず、B1セルにACCESSファイルのパスを入力します。

ACCESSファイルパスを入力する

次に、B2セルにクエリを入力します。

クエリを入力する

注意点としては、FROMの前やWHEREの前に空白を入力して、前の項目とくっつかないように入力しましょう。

例えば、「金額」と「FROM」がくっつくと無効なクエリとなります。

また、便利な使い方としては、メインシートの「A1・A2・B1・B2」以外は、

VBAプログラムでは利用していないので、よく使うクエリを入力して保存しておきましょう。

そうすれば、使いたいクエリをコピーして、B2セルに貼り付けるだけで、クエリを切り替えることができます。

または、B2セルに「=C2」と入力すれば、C2セルに入力されているクエリを利用できます。

次に、「開発」タブの「マクロ」をクリックします。

テーブル名とクエリ名を取得するVBAプログラムを実行する

先程作成したVBAプログラム「GetData」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックしてデータを取得します。

実行ボタンをクリックする

しばらく待つと、指定したACCESSファイルから指定したクエリデータが「データ」シートに表示されます。

１行目には指定したクエリの各項目名が表示され、２行目以降には取得したデータが表示されます。

ACCESSからデータを取得した

実際のACCESSは、以下のようになっています。

実際のACCESSの様子

売上明細テーブルから、日付＝20220602のデータのみ取得できています。

クエリの条件を変更すれば、違ったデータも簡単に取得できます。

例えば、以下は担当者、商品名毎の合計金額を取得するクエリに変更して実行しています。

クエリを変更して実行

きちんと、データが取得できています。

クエリを変更して実行した結果

ACCESSからクエリを指定してデータを取得するVBAプログラム（GetData）の説明

それでは、ACCESSからクエリを指定してデータを取得するVBAプログラム（GetData）を説明します。

①メインシートを変数に格納する

'①メインシートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")

1 2	'①メインシートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン")

ここでは、このあとの処理で何度も使う「メイン」シートを変数に格納しています。

②データシートを変数に格納する

'②データシートを変数に格納する
Set shtData = ThisWorkbook.Sheets("データ")

1 2	'②データシートを変数に格納する Set shtData = ThisWorkbook.Sheets("データ")

ここでは、このあとの処理で何度も使う「データ」シートを変数に格納しています。

③データシートをクリアする

'③データシートをクリアする
shtData.Cells.ClearContents

1 2	'③データシートをクリアする shtData.Cells.ClearContents

ここでは、クエリデータを取得する前に、データシートに入力されているデータをクリアしています。

前回実行して取得したデータを、事前にクリアするためです。

④ADODBコネクションオブジェクトを生成する

'④ADODBコネクションオブジェクトを生成する
Set con = CreateObject("ADODB.Connection")

1 2	'④ADODBコネクションオブジェクトを生成する Set con = CreateObject("ADODB.Connection")

ここでは、ACCESSに接続するオブジェクトを生成しています。

このオブジェクトを利用して、ACCESSからクエリデータ取得することができます。

⑤指定されているACCESSに接続する

'⑤指定されているACCESSに接続する
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & shtMain.Range("B1")

1 2	'⑤指定されているACCESSに接続する con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & shtMain.Range("B1")

ここでは、メインシートのB1セルに入力されているACCESSに接続しています。

⑥指定されているクエリでACCESSからデータを取得する

'⑥指定されているクエリでACCESSからデータを取得する
Set rs = con.Execute(shtMain.Range("B2"))

1 2	'⑥指定されているクエリでACCESSからデータを取得する Set rs = con.Execute(shtMain.Range("B2"))

ここでは、メインシートのB2セルに入力されているクエリを利用してACCESSからデータを取得しています。

⑦取得したデータのフィールド名をデータシートにセットする

'⑦取得したデータのフィールド名をデータシートにセットする
For i = 0 To rs.Fields.Count - 1
    shtData.Cells(1, i + 1) = rs.Fields(i).Name
Next

'⑦取得したデータのフィールド名をデータシートにセットする

For i = 0 To rs.Fields.Count - 1

shtData.Cells(1, i + 1) = rs.Fields(i).Name

ここでは、取得したデータの項目名をデータシートの1行目に出力しています。

⑧取得したデータをデータシートに貼り付けする

'⑧取得したデータをデータシートに貼り付けする
shtData.Range("A2").CopyFromRecordset rs

1 2	'⑧取得したデータをデータシートに貼り付けする shtData.Range("A2").CopyFromRecordset rs

ここでは、取得したデータをデータシートの2行目に貼り付けています。

⑨使用したオブジェクトを閉じる

'⑨使用したオブジェクトを閉じる
rs.Close
con.Close

'⑨使用したオブジェクトを閉じる

rs.Close

con.Close

ここでは、使用して使い終わったオブジェクトを閉じています。

⑩使用したオブジェクトをメモリから開放する

'⑩使用したオブジェクトをメモリから開放する
Set rs = Nothing
Set con = Nothing

'⑩使用したオブジェクトをメモリから開放する

Set rs = Nothing

Set con = Nothing

ここでは、使い終わったオブジェクトをメモリから解放しています。

ということで、ACCESSからクエリを指定してデータを取得できるようになりました。

それでは、ACCESSから欲しいデータを取得するツールを作って、作業を効率化しましょう。

以上です。

「機能概要図」（ACCESSからクエリを指定してデータを取得する）

ACCESSファイルパス指定欄、クエリ指定欄、取得したデータの表示欄を準備する

「メイン」シートを作成する

「データ」シートを作成する

VBAでACCESSからクエリを指定してデータを取得する機能を作成する

ACCESSからクエリを指定してデータを取得する機能の使い方

ACCESSからクエリを指定してデータを取得するVBAプログラム（GetData）の説明

①メインシートを変数に格納する

②データシートを変数に格納する

③データシートをクリアする

④ADODBコネクションオブジェクトを生成する

⑤指定されているACCESSに接続する

⑥指定されているクエリでACCESSからデータを取得する

⑦取得したデータのフィールド名をデータシートにセットする

⑧取得したデータをデータシートに貼り付けする

⑨使用したオブジェクトを閉じる

⑩使用したオブジェクトをメモリから開放する

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル