この記事では、Excelのマクロを使ってフォルダ内の全Accessファイルのフォームにあるコントロールの値集合ソースの値を取得する方法についてご説明します。
マクロの説明に入る前に、まずは次の動画をご覧ください。
まずは、指定されたトップディレクトリからサブフォルダ含めて全てのフォルダにあるAccessのデータベースファイルをすべて取得して配列に格納しておきます。
配列への格納処理が終わったら、その配列に格納されたAccessのデータベースファイルを1つ開きます。
ファイルが開いたら、フォームを開いてコンボボックスまたはリストボックスに対して「値集合ソース」に設定された値を全て取得してExcelのシートに出力しています。
取得後は開いたフォームを閉じます。
フォームが複数ある場合は、フォームの数だけ同じ処理を繰り返します。
1つAccessのデータベースファイルからすべての値集合ソースの値が取得できたら、配列に格納された残りのAccessのデータベースファイルもすべて同じ処理を繰り返し「値集合ソース」を取得します。
値集合ソースとは、Accessのフォームに設置されたコンボボックスまたはリストボックスの設定値の一つです。
今回紹介するのは以前紹介したマクロの改良版
今回の記事で紹介するマクロは、以下の記事でご紹介したマクロの改良版です。
【ExcelVBA】Excelのマクロを使ってAccessのフォームにあるコントロールの値集合ソースの値を取得するには
改良前と改良後では以下の違いがあります。
- 改良前：単一のAccessのデータベースファイルを対象
- 改良後(今回のマクロ)：サブフォルダ含めて全てのフォルダにあるAccessのデータベースファイルを対象
改良前は単一のAccessのデータベースファイルだけ値集合ソースの値を取得します。
今回の改良後のマクロでは、サブフォルダ含めて全てのフォルダにあるAccessのデータベースファイルを対象に値集合ソースの値を取得するように改良しました。
そもそもなぜ今回のマクロを作成したのかその目的は、複数のAccessのデータベースファイルが存在してる場合に1つのファイルだけしか値集合ソースの値が取得できないのは不便だろうと思ったからです。
複数のAccessのデータベースファイルからまとめて値集合ソースが取得できた方が便利かなと思い今回のマクロを作成しました。(1つのファイルだけ値集合ソースの値が取得できれば良いという場合は、改良前のマクロを参照していただければと思います)
例えば今回のマクロでは、「file」というフォルダの中に複数のAccessのデータベースファイルが存在する場合は、そのすべてのファイルに対してマクロが値集合ソースの値を取得します。
取得した結果は下の通りです。
取得した値集合ソースの値はI列に出力されています。
なお今回のマクロでは、値集合ソースの値以外に、どのAccessのデータベースファイルなのか、どのフォームなのかも分かるようシートに出力しています。
【動画】Excelのマクロを使ってフォルダ内の全Accessファイルのフォームにあるコントロールの値集合ソースの値を取得する実際の動き
マクロ作成の流れ
今回のサンプルでは、「値集合ソース」だけでなく、どのフォームにあるコントロールの「値集合ソース」なのか、コントロールの型(コンボボックスやリストボックスなど)も取得してExcelのシートに出力します。
ExcelファイルとAccessのデータベースのフォームの例
今回は次のExcelファイルを用意しました。
項番、パス、ファイル名、フォーム名、コントロール名、コントロールのタイプ、値集合ソースの設定値の項目を出力する表と、Accessのデータベースファイルのフルパスを入力するセル(黄色のセル)、そして実行ボタンが設置されています。
また、Accessデータベースファイルと、フォームに設置されたコントロールを用意しました。
コンボボックスとリストボックスには、値集合ソースに値が設定されています。
C2の黄色のセルにAccessのデータベースファイルが格納されているトップフォルダを入力してマクロを実行すると、サブフォルダ含めて全てのフォルダにあるAccessのデータベースファイルのフォームにあるコンボボックスとリストボックスの値集合ソースの設定値(と項番、パス、ファイル名、フォーム名、コントロール名、コントロールのタイプ)がExcelのシートに出力されます。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim aryCnt As Integer '配列用カウンタ
Dim rowCnt As Integer 'セルの行用カウンタ用変数
Dim ws As Worksheet 'ワークシート変数
Dim FolderPath As String 'Accessのデータベースファイルの置き場
Dim fType() As Variant 'Accessのデータベースの種類(「mdb」と「accdb」)
Dim dbFileAry() As String 'Accessのデータベースのファイル名の格納用配列
Dim fso As Object 'FileSystemObjectオブジェクトのインスタンス用変数
Dim accApp As Object 'Accessアプリケーション参照用変数
Dim db As Object 'Accessのデータベース用変数
Dim frm As Object 'フォーム用変数
Dim ctl As Object 'コントロール用変数
Dim rwSrc As String '「値集合ソース」の値格納用変数
Const rowBgnPos As Integer = 5 '表のデータ開始行
'カウンタを初期化する
aryCnt = 0
rowCnt = rowBgnPos
'シートを取得する
Set ws = Worksheets("work")
'Accessのデータベースファイルを検索する対象のトップフォルダのパスを取得する
FolderPath = ws.Range("dirPath").Value
'ファイルの種類を取得する(Accessのデータベースファイルの「mdb」と「accdb」)
fType = Array("mdb", "accdb")
'Accessのデータベースファイルを検索するサブルーチンを呼び出す
Call fileSearch(FolderPath, dbFileAry, aryCnt, fType)
'セルをクリアする
ws.Range("C5:I30").ClearContents
If UBound(dbFileAry) = 0 Then
'Accessのデータベースファイルが存在しない場合
MsgBox "Accessのデータベースファイルが見つかりませんでした。"
Exit Sub
End If
'カウンタを初期化する
aryCnt = 0
'FileSystemObjectオブジェクトのインスタンスを作成する
Set fso = CreateObject("Scripting.FileSystemObject")
For aryCnt = 0 To UBound(dbFileAry)
'Accessアプリケーションを開始する
Set accApp = CreateObject("Access.Application")
'Accesのデータベースを開く
accApp.OpenCurrentDatabase dbFileAry(aryCnt), False
'データベースオブジェクトを取得する
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」の場合
'項番をDのセルに出力する
ws.Range("C" & rowCnt).Value = (rowCnt - rowBgnPos) + 1
'フォルダのパスをDのセルに出力する
ws.Range("D" & rowCnt).Value = fso.GetParentFolderName(dbFileAry(aryCnt))
'フォルダ名をEのセルに出力する
ws.Range("E" & rowCnt).Value = fso.GetFileName(dbFileAry(aryCnt))
'フォーム名をFのセルに出力する
ws.Range("F" & rowCnt).Value = frm.Name
'コントロール名をGのセルに出力する
ws.Range("G" & rowCnt).Value = ctl.Name
'コントロールのタイプをHのセルに出力する
ws.Range("H" & rowCnt).Value = TypeName(ctl)
'「値集合ソース」の値を取得する
rwSrc = accApp.Forms(frm.Name).Controls(ctl.Name).rowSource
'「値集合ソース」の値をIのセルに出力する
ws.Range("I" & rowCnt).Value = rwSrc
rowCnt = rowCnt + 1
End Select
DoEvents
Next ctl
'フォームを閉じる
accApp.DoCmd.Close acForm, frm.Name, acSaveNo
Next frm
'Accessを閉じる
accApp.Quit acQuitSaveNone
Next aryCnt
'後処理
Set ctl = Nothing
Set frm = Nothing
Set db = Nothing
Set accApp = Nothing
End Sub
Sub fileSearch(FolderPath As String, dbFileAry() As String, aryCnt As Integer, fType() As Variant)
Dim fso As Object 'FileSystemObjectオブジェクトのインスタンス用変数
Dim Folder As Object 'フォルダ用変数
Dim FileItem As Object '取得したファイル用変数
Dim fTypeCnt As Integer '配列fTypeのカウンタ用変数
Dim SubFolder As Object 'サブフォルダ用変数
'FileSystemObjectオブジェクトのインスタンスを作成する
Set fso = CreateObject("Scripting.FileSystemObject")
'GetFolderメソッドを使用して、指定したパスのフォルダを取得する
Set Folder = fso.GetFolder(FolderPath)
'フォルダ内にあるファイルの数分処理を繰り返す
For Each FileItem In Folder.Files
'配列fTypeの要素数分処理を繰り返す
For fTypeCnt = 0 To UBound(fType)
If fso.GetExtensionName(FileItem.path) = fType(fTypeCnt) Then
'拡張子が「fType(fTypeCnt)」に格納されているもの(「mdb」か「accdb」)と一致する場合
'動的配列の再宣言を行う(配列に格納されている値を残したまま)
ReDim Preserve dbFileAry(aryCnt)
'Accessのデータベースファイルを配列に格納する
dbFileAry(aryCnt) = FileItem.path
aryCnt = aryCnt + 1
End If
Next
Next FileItem
'パスのサブフォルダを対象にループする
For Each SubFolder In Folder.SubFolders
'本サブルーチンを再帰呼び出しする
Call fileSearch(SubFolder.path, dbFileAry, aryCnt, fType)
Next SubFolder
End Sub
注目すべきコード①
最初に見て頂きたいのは27行目です。
'Accessのデータベースファイルを検索する対象のトップフォルダのパスを取得する
FolderPath = ws.Range("dirPath").Value
コードの説明
以上のコードは、Accessのデータベースファイルの置き場を取得しているコードです。
Accessのデータベースファイルを検索する対象のトップフォルダのパスの取得は、C2の黄色のセルに入力されたパスから取得します。
※C2の黄色のセルには「dirPath」という名前を付けています。
注目すべきコード②
次に見て頂きたいのは31行目です。
'ファイルの種類を取得する(Accessのデータベースファイルの「mdb」と「accdb」)
fType = Array("mdb", "accdb")
以上のコードは、Accessのデータベースファイルの種類の拡張子を2つ用意しているコードです。
拡張子が「mdb」と「accdb」のどちらもAccessのデータベースファイルなので、この2つの拡張子の文字列「fType」を配列に格納しておき、フォルダ内の全てのファイルから、Accessのデータベースファイルを検索するのに使います。
ちなみにフォルダ内の全ファイルから「mdb」または「accdb」の拡張子が付いたファイルを検索している処理は以下の151行目です。
If fso.GetExtensionName(FileItem.path) = fType(fTypeCnt) Then
GetExtensionNameメソッドでファイルから拡張子を取得し、その取得した拡張子が配列「fType」に格納された「mdb」または「accdb」の文字列と一致するかを判定しています。
もし拡張子が一致すれば、ファイルがAccessのデータベースファイルであることが特定できます。
注目すべきコード③
次に見て頂きたいのは34行目です。
'Accessのデータベースファイルを検索するサブルーチンを呼び出す
Call fileSearch(FolderPath, dbFileAry, aryCnt, fType)
Accessのデータベースファイルを検索するサブルーチン「fileSearch」を呼び出す処理です。
「fileSearch」には以下の引数を渡して呼び出します。
- FolderPath：Accessのデータベースファイル名の検索先フォルダ
- dbFileAry：Accessのデータベースファイル名を格納する配列
- aryCnt：Accessのデータベースファイル名を格納する配列の要素数用変数
- fType：Accessのデータベースファイルの拡張子「mdb」と「accdb」の文字列を格納した配列
FolderPath
FolderPathにはAccessのデータベースファイル名の検索先フォルダを指定します。
FolderPathで指定されたフォルダを「fileSearch」に渡し、「fileSearch」でAccessのデータベースファイルが存在するか検索します。
dbFileAry
「fileSearch」に配列「dbFileAry」を渡し、「fileSearch」でAccessのデータベースファイルを検索して見つかったら配列「dbFileAry」に格納します。
aryCnt
aryCntは、配列「dbFileAry」の要素数に使う変数です。
Accessのデータベースファイルを検索して見つかった場合に配列「dbFileAry」に格納しますが、格納するには配列「dbFileAry」の要素数を1つ増やしてから新たに見つかったAccessのデータベースファイルを格納します。
そのため、配列「dbFileAry」の要素数を拡張するときは、このaryCntの値を変更します。
aryCntの値を変更は、Accessのデータベースファイルが見つかったら、aryCntの値を1つ増やします。
aryCnt = aryCnt + 1
配列「dbFileAry」の要素数を拡張しないと新たなAccessのデータベースファイルが格納できないので、要素数を拡張するのにaryCnt1つ増やします。
fType
fTypeにはAccessのデータベースファイルの拡張子の文字列「mdb」と「accdb」が格納されています。
拡張子の文字列に「mdb」または「accdb」が使われているファイルかどうかを「fileSearch」で特定するため、「fileSearch」に「fType」を渡します。
注目すべきコード④
次に見て頂きたいのは140行目から143行目です。
'FileSystemObjectオブジェクトのインスタンスを作成する
Set fso = CreateObject("Scripting.FileSystemObject")
'GetFolderメソッドを使用して、指定したパスのフォルダを取得する
Set Folder = fso.GetFolder(FolderPath)
以上のコードは、FileSystemObjectのインスタンスを生成し、フォルダとファイルを検索したいフォルダのトップ階層のパスのフォルダを取得する処理のコードです。
FileSystemObjectのインスタンスのGetFolderメソッドに、フォルダとファイルを検索したいフォルダのトップ階層のパスを指定して実行することで、パス内のフォルダ名やファイル名を取得できます。
注目すべきコード⑤
次に見て頂きたいのは146行目から167行目です。
'配列fTypeの要素数分処理を繰り返す
For fTypeCnt = 0 To UBound(fType)
If fso.GetExtensionName(FileItem.path) = fType(fTypeCnt) Then
'拡張子が「fType(fTypeCnt)」に格納されているもの(「mdb」か「accdb」)と一致する場合
'動的配列の再宣言を行う(配列に格納されている値を残したまま)
ReDim Preserve dbFileAry(aryCnt)
'Accessのデータベースファイルを配列に格納する
dbFileAry(aryCnt) = FileItem.path
aryCnt = aryCnt + 1
End If
Next
コードの説明
以上のコードは、フォルダ内にあるファイルにAccessのデータベースファイルが存在していれば、配列「dbFileAry」にAccessのデータベースファイルを格納する処理のコードです。
コードの詳細
146行目のコードは、フォルダ内のファイルの数分だけ処理を繰り返すForです。
149行目のコードは、配列「fType」の要素数分処理を繰り返すFor文です。
配列fTypeには「mdb」と「accdb」の2つの文字列が格納されているので、For文内の処理は2回繰り返されます。
151行目のコードでは、GetExtensionNameメソッドでファイルから拡張子を取得し、その取得した拡張子が配列「fType」に格納された「mdb」または「accdb」の文字列と一致するかを判定しています。
一致していれば156行目のコードが実行されます。
151行目のコードでは、ReDim Preserveを使って、配列「dbFileAry」の要素数を再定義しています。
配列「dbFileAry」にAccessのデータベースファイルを1つ格納したら、Accessのデータベースファイルが新たに見つかった場合に、配列「dbFileAry」の要素数を1つ増やす必要があります。
なのでReDim Preserveを使って、配列「dbFileAry」の要素数を再定義(要素数を1つ拡張)してAccessのデータベースファイルを配列「dbFileAry」に格納できるようにしておきます。
159行目のコードでは、新たに見つかったAccessのデータベースファイルを配列「dbFileAry」に格納します。
161行目のコードでは、配列「dbFileAry」の要素数を再定義(要素数を1つ拡張)するのに必要なaryCntの値を1つ増やします。
注目すべきコード⑥
次に見て頂きたいのは170行目から175行目です。
'パスのサブフォルダを対象にループする
For Each SubFolder In Folder.SubFolders
'本サブルーチンを再帰呼び出しする
Call fileSearch(SubFolder.path, dbFileAry, aryCnt, fType)
Next SubFolder
コードの説明
以上のコードは、サブフォルダが存在する間は自分自身のサブルーチンを繰り返し呼び出す再帰処理を行っているコードです。
再帰処理とは、サブルーチンの処理が行われている途中で、強制的に自分自身のサブルーチンを呼び出して再度サブルーチンの最初から処理を行わせることを言います。
強制的に自分自身のサブルーチンを呼び出しているのは173行目です。
173行目でfileSearchが呼び出されると、fileSearchの処理の途中で131行目(fileSearchの最初)から強制的に開始されます。
この再帰処理(fileSearch処理中の再呼び出し)は、170行目のFor文内で繰り返し行われますが、このFor文が終わらないかぎりfileSearchが再度呼び出されます。
なお、For文から抜ける条件は、すべてのサブフォルダの参照が終わることです。(引数にサブフォルダを指定してfileSearchを呼び出す)
すべてのサブフォルダを参照すればFor文を抜けます。
For文から抜ければfileSearchが呼び出されることがなくなるので(再帰処理が行われない)、fileSearchのEnd Subまで処理が進みfileSearchの処理を抜けて、fileSearchの呼び出し元の34行目に遷移します。
そのまま34行目の次の行に処理が遷移します。
注目すべきコード⑦
次に見て頂きたいのは39行目から47行目です。
If UBound(dbFileAry) = 0 Then
'Accessのデータベースファイルが存在しない場合
MsgBox "Accessのデータベースファイルが見つかりませんでした。"
Exit Sub
End If
以上のコードは、Accessのデータベースファイルが存在しない場合に処理をマクロを終了させるコードです。
もしAccessのデータベースファイルが存在しない場合はマクロを終了させたいので、Accessのデータベースファイル名を格納する配列「dbFileAry」の要素数が0の場合は処理を終了させます。
Accessのデータベースファイルが存在しない場合は配列「dbFileAry」に何も格納されないので、要素数は0です。
注目すべきコード⑧
次に見て頂きたいのは58行目から64行目です。
'Accessアプリケーションを開始する
Set accApp = CreateObject("Access.Application")
'Accesのデータベースを開く
accApp.OpenCurrentDatabase dbFileAry(aryCnt), False
'データベースオブジェクトを取得する
Set db = accApp.CurrentDb()
コードの説明
以上のコードは、Accessのデータベースファイルを開き、Accessのデータベースの操作を行うのに必要なインスタンスを生成する処理のコードです。
今回のコードでは、生成したインスタンス「db」を使い、Accessのデータベースにあるフォームを取得します。
※インスタンス「accApp」はAccessのアプリケーションに対する操作を、「db」はAccessのデータベースのテーブルやクエリなどを操作するのに使います。
なお、「db」を使ったAccessのデータベースにあるフォームの取得は、「注目すべきコード⑨」で行います。
注目すべきコード⑨
次に見て頂きたいのは67行目です。
'Accessのデータベースにあるフォーム分だけ処理を繰り返す
For Each frm In db.Containers("Forms").Documents
コードの説明
以上のコードは、Accessのデータベースに存在するフォームの数だけ繰り返しフォームを取得するFor文です。
Containersに「Forms」を指定すると、DocumentsコレクションがAccessのデータベースにあるフォームを返します。(frmに、取得したAccessのデータベースにあるフォームが格納されます)
注目すべきコード⑩
次に見て頂きたいのは70行目です。
'フォームを開く
accApp.DoCmd.OpenForm frm.Name, acNormal
コードの説明
以上のコードは、「注目すべきコード⑨」で取得したフォームを開く処理のコードです。
OpenFormメソッドの引数に、取得したAccessのデータベースにあるフォームの名前を指定して実行することでフォームが開きます。
フォームを開くことでフォームにあるコントロールの情報を取得できるようになります。
注目すべきコード⑪
次に見て頂きたいのは73行目から111行目です。
'フォームにあるすべてのコントロールをループする
For Each ctl In accApp.Forms(frm.Name).Controls
Select Case TypeName(accApp.Forms(frm.Name).Controls(ctl.Name))
Case "ComboBox", "ListBox"
'コントロールが「ComboBox」か「ListBox」の場合
'項番をDのセルに出力する
ws.Range("C" & rowCnt).Value = (rowCnt - rowBgnPos) + 1
'フォルダのパスをDのセルに出力する
ws.Range("D" & rowCnt).Value = fso.GetParentFolderName(dbFileAry(aryCnt))
'フォルダ名をEのセルに出力する
ws.Range("E" & rowCnt).Value = fso.GetFileName(dbFileAry(aryCnt))
'フォーム名をFのセルに出力する
ws.Range("F" & rowCnt).Value = frm.Name
'コントロール名をGのセルに出力する
ws.Range("G" & rowCnt).Value = ctl.Name
'コントロールのタイプをHのセルに出力する
ws.Range("H" & rowCnt).Value = TypeName(ctl)
'「値集合ソース」の値を取得する
rwSrc = accApp.Forms(frm.Name).Controls(ctl.Name).rowSource
'「値集合ソース」の値をIのセルに出力する
ws.Range("I" & rowCnt).Value = rwSrc
rowCnt = rowCnt + 1
End Select
DoEvents
Next ctl
コードの説明
以上のコードは、開いたフォームにあるコンボボックスまたはリストボックスの「値集合ソース」を取得してExcelのシートに出力している処理のコードです。
なお、ここではコントロールの情報を取得するのと同時に、項番、パス、ファイル名、フォーム名、コントロール名、コントロールのタイプも取得しています。
コードの詳細
73行目のコードでは、フォームにあるコントロールの数だけループするFor文です。
75行目と77行目のコードでは、コントロールがコンボボックスまたはリストボックスであるかを判定し、コンボボックスまたはリストボックスの場合は81行目から「値集合ソース」や他に必要な情報を出力します。
82行目のコードでは、項番をDのセルに出力しています。
85行目のコードでは、フォルダのパスをDのセルに出力しています。
88行目のコードでは、フォルダ名をEのセルに出力しています。
91行目のコードでは、フォーム名をFのセルに出力しています。
94行目のコードでは、コントロール名をGのセルに出力しています。
97行目のコードでは、コントロールのタイプをHのセルに出力しています。
100行目のコードでは、「値集合ソース」の値をrowSourceプロパティから取得し、103行目のコードでIのセルに「値集合ソース」の値を出力しています。
注目すべきコード⑫
次に見て頂きたいのは113行目です。
'フォームを閉じる
accApp.DoCmd.Close acForm, frm.Name, acSaveNo
コードの説明
以上のコードは、開いたフォームを閉じる処理のコードです。
注目すべきコード⑬
次に見て頂きたいのは119行目です。
'Accessを閉じる
accApp.Quit acQuitSaveNone
動作確認
「ExcelファイルとAccessのデータベースのフォームの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft Access 16.0 Object Library(MSACC.OLB)
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「MSACC.OLB」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、参照設定しないと動かない程度に思って頂ければと思います。
最後に
この記事では、Excelのマクロを使ってフォルダ内の全Accessファイルのフォームにあるコントロールの値集合ソースの値を取得する方法についてご説明しました。
値集合ソースの値を取得するにはコントロールを選択してプロパティシート上で確認しないといけないので手間がかかります。
なので、Accessのフォームにあるコントロールの値集合ソースの値を取得したいときは本記事を参考に試してみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
