この記事では、指定したACCESSファイルからテーブルデータおよびクエリデータを取得する方法について説明しています。
1つ1つテーブルやクエリを開いて、コピーするのは大変な作業です。
今回ご紹介する機能を利用すれば、ACCESSファイルを指定し、テーブル名またはクエリ名を指定するだけで、テーブルデータやクエリデータを一瞬で取得することができます。
ACCESSに存在するテーブルやクエリの名前を全て取得する機能もあります。
それでは、指定したACCESSからテーブルやクエリのデータを取得する機能を作成していきましょう。
困っている女性
困っている女性
困っている女性
解決する男性
解決する男性
困っている女性
もくじ
「機能概要図」(ACCESSからテーブル・クエリデータを取得する)
今回作成する機能の概要図は、以下です。
今回作成する機能には、以下の2つの機能があります。
- 指定したACCESSファイルのテーブル名一覧とクエリ名一覧を取得し、EXCELシートに出力する
- 指定したACCESSファイルの指定したテーブル(クエリ)からデータを取得し、EXCELシートに出力する
指定したACCESSファイルから、テーブルやクエリデータを取得する機能を、1つ1つ順番に説明していきます。
ACCESSファイルパス指定欄、テーブル名・クエリ名指定欄、取得したデータの表示欄を準備する
まず、ACCESSファイルとテーブル名・クエリ名を指定する欄を作成します。
次に、取得したテーブル名とクエリ名を表示する欄を作成します。
そのあと、指定したテーブルまたはクエリから取得したデータを表示するシートを作成します。
「メイン」シートを作成する
- 「メイン」という名前のシートを作成します。
- A1セルに「ACCESSファイルパス」のタイトル欄を作成します。
- B1セルに「テーブル名・クエリ名」のタイトル欄を作成します。
- A2とB2セルに、「ACCESSファイルパス」と「テーブル名・クエリ名」を指定する欄を作成します。
- A3とB3セルに、「テーブル名」と「クエリ名」のタイトル欄を作成します。
- 4行目以降は、取得したテーブル名およびクエリ名を出力する欄になります。
4行目以降のA列とB列には、VBAプログラムが記入してくれます。
A列にはテーブル名、B列にクエリ名が表示されます。
「データ」シートを作成する
- 「データ」という名前のシートを作成します。
- あとは、特になにもしなくてよいです。
1行目には、テーブルまたはクエリの項目名が表示されます。
2行目以降には、データが表示されます。
VBAでACCESSからテーブル名およびクエリ名を取得する機能を作成する
開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。
「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。
標準モジュールを追加して、以下のソースを書きます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
Public Sub GetTableList() Dim shtMain As Worksheet Dim MaxRow As Integer Dim ca As Object Dim tb As Object Dim cnstr As String Dim cntTable As Integer Dim cntQuery As Integer '①メインシートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン") '②データ入力されているA列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row '③最終行が4行目以降なら、4行目以降のA列をクリアする If MaxRow >= 4 Then shtMain.Range("A4:A" & MaxRow).ClearContents End If '④データ入力されているB列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 2).End(xlUp).Row '⑤最終行が4行目以降なら、4行目以降のB列をクリアする If MaxRow >= 4 Then shtMain.Range("B4:B" & MaxRow).ClearContents End If '⑥ACCESSの接続文字列を作成する cnstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & shtMain.Range("A2") '⑦ADOX.Catalogオブジェクトを生成する Set ca = CreateObject("ADOX.Catalog") '⑧⑥で指定したACCESSに接続する ca.ActiveConnection = cnstr cntTable = 4 cntQuery = 4 '⑨ACCESSに存在するオブジェクトに対して以降の処理を行う For Each tb In ca.Tables '⑩オブジェクトがテーブルの場合、メインシートのA列にテーブル名をセットする If tb.Type = "TABLE" Then shtMain.Cells(cntTable, 1) = tb.Name cntTable = cntTable + 1 End If '⑪オブジェクトがクエリの場合、メインシートのB列にクエリ名をセットする If tb.Type = "VIEW" Then shtMain.Cells(cntQuery, 2) = tb.Name cntQuery = cntQuery + 1 End If Next MsgBox "完了" End Sub |
次に、EXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。
VBAでACCESSからテーブルおよびクエリからデータを取得する機能を作成する
次に、ACCESSからテーブルおよびクエリからデータを取得する機能をVBAプログラムで作成します。
標準モジュールにVBAプログラムを入力します。
以下のソースを書きます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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("A2") '⑥指定されているテーブル(クエリ)のデータを取得する Set rs = con.Execute("SELECT * FROM " & 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 |
EXCELファイルを上書き保存します。
ACCESSからテーブル名およびクエリ名を取得する機能の使い方
それでは、ACCESSに存在する全てのテーブル名およびクエリ名を取得する方法を説明します。
まず、A2セルにACCESSファイルのパスを入力します。
次に、「開発」タブの「マクロ」をクリックします。
先程作成したVBAプログラム「GetTableList」が一覧に表示されていますので、選択します。
次に、「実行」ボタンをクリックしてテーブル名とクエリ名を取得します。
しばらく待つと、指定したACCESSファイルに存在するテーブル名とクエリ名が「メイン」シートの4行目以降に表示します。
A列にはテーブル名、B列にはクエリ名が表示されます。
ACCESSからテーブルまたはクエリからデータを取得する機能の使い方
それでは、指定したACCESSの指定したテーブル(クエリ)からデータを取得する方法を説明します。
B2セルにテーブル名またはクエリ名を入力します。
テーブル名またはクエリ名がわかっている場合は、先程のテーブル名およびクエリ名を取得する操作は必要ありません。
次に、「開発」タブの「マクロ」をクリックします。
先程作成したVBAプログラム「GetData」が一覧に表示されていますので、選択します。
次に、「実行」ボタンをクリックしてテーブルまたはクエリデータを取得します。
しばらく待つと、「データ」シートに指定したテーブル(クエリ)のデータが表示されます。
1行目には、各項目名が表示されます。
2行目以降は、データが表示されます。
実際のACCESSは、以下のようになっています。
全てのテーブル名およびクエリ名を取得できています。
また、指定したテーブルからデータを取得できました。
テーブル名およびクエリ名を取得するVBAプログラム(GetTableList)の説明
それでは、テーブル名とクエリ名を取得するVBAプログラム(GetTableList)を説明します。
①メインシートを変数に格納する
1 2 |
'①メインシートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン") |
そうすることにより、入力するプログラムを短くすることができるからです。
何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。
②データ入力されているA列の最終行を取得する
1 2 |
'②データ入力されているA列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row |
何度も実行していると、前回実行時に取得したテーブル名が残っています。
今回取得した情報を出力する前に、その情報をクリアするために最終行を取得しています。
最終行を取得することで、どこまでのセルをクリアするべきかがわかるからです。
③最終行が4行目以降なら、4行目以降のA列をクリアする
1 2 3 4 |
'③最終行が4行目以降なら、4行目以降のA列をクリアする If MaxRow >= 4 Then shtMain.Range("A4:A" & MaxRow).ClearContents End If |
その場合、A4セルからA列の最終行までをクリアします。
この処理によって、すでに入力されているテーブル名がクリアされます。
④データ入力されているB列の最終行を取得する
1 2 |
'④データ入力されているB列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 2).End(xlUp).Row |
前回実行時のクエリ名が残っている可能性があるので、クリアしておくためにB列の最終行を取得しています。
⑤最終行が4行目以降なら、4行目以降のB列をクリアする
1 2 3 4 |
'⑤最終行が4行目以降なら、4行目以降のB列をクリアする If MaxRow >= 4 Then shtMain.Range("B4:B" & MaxRow).ClearContents End If |
その場合、B4セルからB列の最終行までをクリアします。
この処理によって、すでに入力されているクエリ名がクリアされます。
⑥ACCESSの接続文字列を作成する
1 2 |
'⑥ACCESSの接続文字列を作成する cnstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & shtMain.Range("A2") |
⑦ADOX.Catalogオブジェクトを生成する
1 2 |
'⑦ADOX.Catalogオブジェクトを生成する Set ca = CreateObject("ADOX.Catalog") |
このオブジェクトを利用して、テーブル名やクエリ名を取得します。
⑧⑥で指定したACCESSに接続する
1 2 |
'⑧⑥で指定したACCESSに接続する ca.ActiveConnection = cnstr |
⑨ACCESSに存在するオブジェクトに対して以降の処理を行う
1 2 |
'⑨ACCESSに存在するオブジェクトに対して以降の処理を行う For Each tb In ca.Tables |
⑩オブジェクトがテーブルの場合、メインシートのA列にテーブル名をセットする
1 2 3 4 5 |
'⑩オブジェクトがテーブルの場合、メインシートのA列にテーブル名をセットする If tb.Type = "TABLE" Then shtMain.Cells(cntTable, 1) = tb.Name cntTable = cntTable + 1 End If |
⑪オブジェクトがクエリの場合、メインシートのB列にクエリ名をセットする
1 2 3 4 5 |
'⑪オブジェクトがクエリの場合、メインシートのB列にクエリ名をセットする If tb.Type = "VIEW" Then shtMain.Cells(cntQuery, 2) = tb.Name cntQuery = cntQuery + 1 End If |
この機能を利用して、ACCESSからテーブル名およびクエリ名を取得してみましょう。
テーブルまたはクエリからデータを取得するVBAプログラム(GetData)の説明
それでは、テーブルまたはクエリからデータを取得するVBAプログラム(GetData)を説明します。
①メインシートを変数に格納する
1 2 |
'①メインシートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン") |
②データシートを変数に格納する
1 2 |
'②データシートを変数に格納する Set shtData = ThisWorkbook.Sheets("データ") |
③データシートをクリアする
1 2 |
'③データシートをクリアする shtData.Cells.ClearContents |
前回実行して取得したデータを、事前にクリアするためです。
④ADODBコネクションオブジェクトを生成する
1 2 |
'④ADODBコネクションオブジェクトを生成する Set con = CreateObject("ADODB.Connection") |
このオブジェクトを利用して、ACCESSからテーブルやクエリデータ取得することができます。
⑤指定されているACCESSに接続する
1 2 |
'⑤指定されているACCESSに接続する con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & shtMain.Range("A2") |
⑥指定されているテーブル(クエリ)のデータを取得する
1 2 |
'⑥指定されているテーブル(クエリ)のデータを取得する Set rs = con.Execute("SELECT * FROM " & shtMain.Range("B2")) |
⑦取得したデータのフィールド名をデータシートにセットする
1 2 3 4 |
'⑦取得したデータのフィールド名をデータシートにセットする For i = 0 To rs.Fields.Count - 1 shtData.Cells(1, i + 1) = rs.Fields(i).Name Next |
⑧取得したデータをデータシートに貼り付けする
1 2 |
'⑧取得したデータをデータシートに貼り付けする shtData.Range("A2").CopyFromRecordset rs |
⑨使用したオブジェクトを閉じる
1 2 3 |
'⑨使用したオブジェクトを閉じる rs.Close con.Close |
⑩使用したオブジェクトをメモリから開放する
1 2 3 |
'⑩使用したオブジェクトをメモリから開放する Set rs = Nothing Set con = Nothing |
ということで、ACCESSからデータを取得できるようになりました。
それでは、ACCESSからテーブルやクエリデータを取得するツールを作って、作業を効率化しましょう。
以上です。
最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。
20210928のように8桁の数字を入力して、その日のデータを取得するクエリがいくつもあるのですが、このコードではそのクエリは取得できませんでした。C2セルに8桁の日付を入れておいて、データを取得するようなコードを紹介してもらえませんか。
クエリを指定してACCESSからデータ取得可能な記事を作成しました。
ご確認をお願いします。
【EXCEL:VBA】ACCESSからクエリを指定してデータを取得したい
https://excel.kuuneruch.com/access-getdata-query/