この記事では、Excelのマクロを使ってAccessのフォームにあるコントロールの値集合ソースの値を取得する方法についてご説明します。
値集合ソースとは、Accessのフォームに設置されたコンボボックスまたはリストボックスの設定値の一つです。
コントロールに設定されている値集合ソースのSELECT文が参照するテーブルの定義にもし変更(フィールド名やテーブル名の変更など)や削除(フィールドやテーブルの削除など)が生じた場合は、値集合ソースのSELECT文を修正しなければなりません。
どの値集合ソースのSELECT文を修正するのか調査用に洗い出す場合に、コントロールの数が多いとすべてを把握するのは手間がかかります。(コントロールを選択してプロパティシート上で確認するので)
そんな手間がかかる作業の負担をできるだけ減らすために、本マクロを使って一気に値集合ソースの値をExcelのシートに書き出します。
【動画】Excelのマクロを使ってAccessのフォームにあるコントロールの値集合ソースの値を取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずはExcelのマクロがAccessのデータベースファイルを開きます。
ファイルが開いたら、フォームを開いてコンボボックスまたはリストボックスに対して「値集合ソース」に設定された値を全て取得してExcelのシートに出力しています。
取得後は開いたフォームを閉じます。
フォームが複数ある場合は、フォームの数だけ同じ処理を繰り返します。
マクロ作成の流れ
今回のサンプルでは、「値集合ソース」だけでなく、どのフォームにあるコントロールの「値集合ソース」なのか、コントロールの型(コンボボックスやリストボックスなど)も取得してExcelのシートに出力します。
ExcelファイルとAccessのデータベースのフォームの例
今回は次のExcelファイルを用意しました。
フォーム名、コントロール名、タイプ、値集合ソースの設定値の4つの項目を出力する表と、Accessのデータベースファイルのフルパスを入力するセル(黄色のセル)、そして実行ボタンが設置されています。
また、Accessデータベースに、下の2つのフォームを用意しました。
フォーム「frm_社員データ」にはコンボボックスとリストボックスは1つもなく、フォーム「frm_社員データ」にはコンボボックスとリストボックスが1つずつ設置されており、「値集合ソース」にSELECT文が設定されています。
C2の黄色のセルにAccessのデータベースファイルの置き場を入力してマクロを実行すると、以上のコンボボックスとリストボックスの値集合ソースの設定値(とフォーム名、コントロール名、タイプ)がExcelのシートに出力されます。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim dbFile As String 'Accessのデータベースファイルの置き場
Dim ws As Worksheet 'ワークシート変数
Dim cnt As Integer 'カウンタ用変数
Dim accApp As Object 'Accessアプリケーション参照用変数
Dim db As Object 'Accessのデータベース用変数
Dim frm As Object 'フォーム用変数
Dim ctl As Object 'コントロール用変数'
Dim rwSrc As String '「値集合ソース」の値格納用変数
'Accessのデータベースファイルの置き場を取得する
dbFile = Worksheets("work").Range("dbFile").Value
'シートを取得する
Set ws = Worksheets("work")
'カウンタを初期化する
cnt = 5
'セルをクリアする
ws.Range("C5:F30").ClearContents
'Accessアプリケーションを開始する
Set accApp = CreateObject("Access.Application")
'Accesのデータベースを開く
accApp.OpenCurrentDatabase dbFile
'データベースオブジェクトを取得する
Set db = accApp.CurrentDb()
'Accessのデータベースにあるフォーム分だけ処理を繰り返す
For Each frm In db.Containers("Forms").Documents
'フォームを開く
accApp.DoCmd.OpenForm frm.Name, acNormal
'フォームにあるすべてのコントロールをループする
For Each ctl In accApp.Forms(frm.Name).Controls
Select Case TypeName(accApp.Forms(frm.Name).Controls(ctl.Name))
Case "ComboBox", "ListBox"
'コントロールが「ComboBox」か「ListBox」の場合
'フォーム名、コントロール名、型を出力する
ws.Range("C" & cnt).Value = frm.Name 'フォーム名
ws.Range("D" & cnt).Value = ctl.Name 'コントロール名
ws.Range("E" & cnt).Value = TypeName(ctl) '型
'「値集合ソース」の値を取得する
rwSrc = accApp.Forms(frm.Name).Controls(ctl.Name).rowSource
'「値集合ソース」の値を出力する
ws.Range("F" & cnt).Value = rwSrc '「値集合ソース」の値
cnt = cnt + 1
End Select
DoEvents
Next ctl
'フォームを閉じる
accApp.DoCmd.Close acForm, frm.Name, acSaveNo
Next frm
'Accessを閉じる
accApp.Quit acQuitSaveNone
'後処理
Set ctl = Nothing
Set frm = Nothing
Set db = Nothing
Set accApp = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは14行目です。
'Accessのデータベースファイルの置き場を取得する
dbFile = Worksheets("work").Range("dbFile").Value
コードの説明
以上のコードは、Accessのデータベースファイルの置き場を取得しているコードです。
Accessデータベースファイルの置き場の取得は、C2の黄色のセルに入力されたAccessデータベースファイルの置き場のフルパスから取得します。
※C2の黄色のセルには「dbFile」という名前を付けています。
このAccessのデータベースファイルのフォームにあるコントロールから「値集合ソース」の値を取得します。
注目すべきコード②
次に見て頂きたいのは27行目から33行目です。
'Accessアプリケーションを開始する
Set accApp = CreateObject("Access.Application")
'Accesのデータベースを開く
accApp.OpenCurrentDatabase dbFile
'データベースオブジェクトを取得する
Set db = accApp.CurrentDb()
コードの説明
以上のコードは、Accessのデータベースファイルを開き、Accessのデータベースの操作を行うのに必要なインスタンスを生成する処理のコードです。
今回のコードでは、生成したインスタンス「db」を使い、Accessのデータベースにあるフォームを取得します。
※インスタンス「accApp」はAccessのアプリケーションに対する操作を、「db」はAccessのデータベースのテーブルやクエリなどを操作するのに使います。
なお、「db」を使ったAccessのデータベースにあるフォームの取得は、「注目すべきコード③」で行います。
注目すべきコード③
次に見て頂きたいのは36行目です。
'Accessのデータベースにあるフォーム分だけ処理を繰り返す
For Each frm In db.Containers("Forms").Documents
コードの説明
以上のコードは、Accessのデータベースに存在するフォームの数だけ繰り返しフォームを取得するFor文です。
Containersに「Forms」を指定すると、DocumentsコレクションがAccessのデータベースにあるフォームを返します。(frmに、取得したAccessのデータベースにあるフォームが格納されます)
注目すべきコード④
次に見て頂きたいのは36行目です。
'フォームを開く
accApp.DoCmd.OpenForm frm.Name, acNormal
コードの説明
以上のコードは、「注目すべきコード③」で取得したフォームを開く処理のコードです。
OpenFormメソッドの引数に、取得したAccessのデータベースにあるフォームの名前を指定して実行することでフォームが開きます。
フォームを開くことでフォームにあるコントロールの情報を取得できるようになります。
注目すべきコード⑤
次に見て頂きたいのは42行目から69行目です。
'フォームにあるすべてのコントロールをループする
For Each ctl In accApp.Forms(frm.Name).Controls
Select Case TypeName(accApp.Forms(frm.Name).Controls(ctl.Name))
Case "ComboBox", "ListBox"
'コントロールが「ComboBox」か「ListBox」の場合
'フォーム名、コントロール名、型を出力する
ws.Range("C" & cnt).Value = frm.Name 'フォーム名
ws.Range("D" & cnt).Value = ctl.Name 'コントロール名
ws.Range("E" & cnt).Value = TypeName(ctl) '型
'「値集合ソース」の値を取得する
rwSrc = accApp.Forms(frm.Name).Controls(ctl.Name).rowSource
'「値集合ソース」の値を出力する
ws.Range("F" & cnt).Value = rwSrc '「値集合ソース」の値
cnt = cnt + 1
End Select
DoEvents
Next ctl
コードの説明
以上のコードは、開いたフォームにあるコンボボックスまたはリストボックスの「値集合ソース」を取得してExcelのシートに出力している処理のコードです。
なお、ここではコントロールの情報を取得するのと同時に、フォーム名、コントロール名、型も取得しています。
コードの詳細
42行目のコードでは、フォームにあるコントロールの数だけループするFor文です。
44行目と46行目のコードでは、コントロールがコンボボックスまたはリストボックスであるかを判定し、コンボボックスまたはリストボックスの場合は52行目でフォーム名(どのフォームにコントロールがあるのか)を、C列のセルに出力しています。
53行目のコードでは、コントロール名をD列のセルに出力しています。
54行目のコードでは、コントロールの型をE列のセルに出力しています。
57行目のコードでは、コントロールの「値集合ソース」をrowSourceプロパティから取得して61行目でF列のセルに出力しています。
注目すべきコード⑥
次に見て頂きたいのは72行目です。
'フォームを閉じる
accApp.DoCmd.Close acForm, frm.Name, acSaveNo
コードの説明
以上のコードは、開いたフォームを閉じる処理のコードです。
注目すべきコード⑦
次に見て頂きたいのは77行目です。
'Accessを閉じる
accApp.Quit acQuitSaveNone
コードの説明
以上のコードは、Accessを閉じる処理のコードです。
動作確認
「ExcelファイルとAccessのデータベースのフォームの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft Access 16.0 Object Library(MSACC.OLB)
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「MSACC.OLB」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、参照設定しないと動かない程度に思って頂ければと思います。
最後に
この記事では、Excelのマクロを使ってAccessのフォームにあるコントロールの値集合ソースの値を取得する方法についてご説明しました。
値集合ソースの値を取得するにはコントロールを選択してプロパティシート上で確認しないといけないので手間がかかります。
なので、Accessのフォームにあるコントロールの値集合ソースの値を取得したいときは本記事を参考に試してみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
