この記事では、Excelのシートにデータ件数分のチェックボックスを追加してAccessのDBの値で制御する方法についてご説明します。
【動画】Excelのシートにデータ件数分のチェックボックスを追加してDBの値で制御する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
マクロがAccessのDBのテーブルデータを取得し、そのデータをシートに出力します。
また、テーブルデータの値を判定し、マクロが自動で生成したチェックボックスにチェックを付けていきます。
今回のサンプルでは、フィールド「フラグ」の値が1の場合はチェックを付け、0の場合はチェックを付けません。
マクロ作成の流れ
今回のサンプルでは、フィールド「フラグ」の値が1の場合はチェックを付け、0の場合はチェックを付けません。
Excelファイルの例
今回は次のExcelファイルを用意しました。
また、今回用意したAccessのDBのテーブルは下の通りです。
マクロを実行すると下の通りに出力されます。
マクロがAccessのDBにある「T_社員」テーブルのデータを取得してシート「data」の表に出力されています。
また、A列にチェックボックスをマクロが生成し、フィールド「フラグ」の値が1の場合はチェックボックスにチェックを付けています。0の場合はチェックを付けません。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim DbName As String 'データベース名
Dim adoCON As ADODB.Connection 'Connection用変数
Dim adoRS As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL文
Dim cnt As Long 'カウンタ
Dim obj As Object 'シェイプ・コントロール用変数
Dim rng As Range 'Rangeオブジェクト格納用変数
Dim cell As Range 'セル用変数
Dim top As Double 'チェックボックスの上端位置を格納する変数
Dim leftPos As Double 'チェックボックスの左端位置を格納する変数
Dim cb As CheckBox 'チェックボックス用変数
'データの先頭行位置
Const bgnRPos As Long = 4
'取得元のテーブル名
Const tblNM As String = "T_社員"
'カレントディレクトリのデータベースパスを取得
DbName = ActiveWorkbook.Path & "\" & "0237.mdb"
'シートをクリアする
Worksheets("data").Range("A3:H1000").ClearContents
With Worksheets("data")
'シート「data」上のシェイプ・コントロールの数分だけループ
For Each obj In .Shapes
If InStr(obj.Name, "test_cbx") > 0 Then
'「test_cbx」の文字列を含むコントロールの場合
'コントロールを削除する
obj.Delete
End If
Next obj
End With
'Connectionオブジェクトのインスタンスを生成する
Set adoCON = CreateObject("ADODB.Connection")
'データベース接続情報の取得
adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" _
& DbName & ""
'Accessに接続する
adoCON.Open
'Recordsetオブジェクトのインスタンスを生成する
Set adoRS = CreateObject("ADODB.Recordset")
'クライアントサイドカーソルに変更
adoRS.CursorLocation = adUseClient
'先ほど取得したデータベース接続情報が設定されているadoCONをActiveConnectionプロパティに設定し、
'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
'⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
adoRS.ActiveConnection = adoCON
'カーソルを開く
adoRS.Open tblNM, adoCON
'Accessからテーブルデータを取得するSQL文を作成
sqlStr = "SELECT "
For cnt = 0 To adoRS.Fields.Count - 1
Select Case True
Case adoRS.Fields.Item(cnt).Name <> "フラグ"
'フィールドが「フラグ」ではない場合
'列名をSELECT文に使う
sqlStr = sqlStr & adoRS.Fields.Item(cnt).Name & ","
'フィールド名をシートに出力する
Worksheets("data").Cells(bgnRPos - 1, cnt + 2).Value = adoRS.Fields.Item(cnt).Name
Case Else
End Select
Next
'末尾の「'」を削除
sqlStr = left(sqlStr, Len(sqlStr) - 1)
sqlStr = sqlStr & " FROM " & tblNM
sqlStr = sqlStr & " ORDER BY 社員番号 ASC"
'フィールド名が取得できたので一度レコードセットを閉じる
adoRS.Close
'SELECT文を実行して、Recordsetオブジェクトにデータをロード
adoRS.Open sqlStr, adoCON
'recordsetの値をシートのセルに出力する
Worksheets("data").Range("B" & bgnRPos).CopyFromRecordset adoRS
'フィールド名が取得できたので一度レコードセットを閉じる
adoRS.Close
'Accessから、チェックボックスの付与を行うのに必要な判断情報を取得するSQL文を作成
sqlStr = "SELECT 社員番号, フラグ FROM " & tblNM
sqlStr = sqlStr & " ORDER BY 社員番号 ASC"
'SELECT文を実行して、Recordsetオブジェクトにデータをロード
adoRS.Open sqlStr, adoCON
'カウンタを初期化する
cnt = 0
'チェックボックスを設置するセルの範囲を取得する
Set rng = Worksheets("data").Range("A" & bgnRPos & ":A" & CStr(bgnRPos + adoRS.RecordCount - 1))
For Each cell In rng
cnt = cnt + 1
'チェックボックスの上端位置を取得する(セルの縦位置の中央に設置)
'→(セルのHeightの中央値に設定し、さらに微調整分マイナスする)
top = cell.top + (cell.Height / 2) - 9
'チェックボックスの左端位置を取得する
leftPos = cell.left
'チェックボックスを生成して配置する
Set cb = Worksheets("data").CheckBoxes.Add(leftPos, top, 0, 0)
'チェックボックスのキャプションを空にする
cb.Caption = ""
'チェックボックスに名前を付ける(重複しないよう項番も付与する)
cb.Name = "test_cbx" & cnt
If adoRS.Fields("フラグ").Value = 1 Then
'「フラグ」という名前のフィールドの値が1の場合
' チェックボックスにチェックを付ける
cb.Value = xlOn
End If
' 次のレコードに移動
adoRS.MoveNext
DoEvents
Next cell
'レコードセットを閉じる
adoRS.Close
'後処理
Set adoCON = Nothing
Set adoRS = Nothing
Set obj = Nothing
Set cb = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは18行目です。
'データの先頭行位置
Const bgnRPos As Long = 4
コードの説明
以上のコードは、取得したAccessのDBのテーブルデータをシートの何行目から出力するのかその開始行を取得するコードです。
今回は4を設定していますが、値が4の場合は4行目からデータが出力されます。
注目すべきコード②
次に見て頂きたいのは24行目です。
'カレントディレクトリのデータベースパスを取得
DbName = ActiveWorkbook.Path & "\" & "0237.mdb"
コードの説明
以上のコードは、データを取得するAccessのDBファイルのフルパスを取得するコードです。
注目すべきコード③
次に見て頂きたいのは29行目から45行目です。
With Worksheets("data")
'シート「data」上のシェイプ・コントロールの数分だけループ
For Each obj In .Shapes
If InStr(obj.Name, "test_cbx") > 0 Then
'「test_cbx」の文字列を含むコントロールの場合
'コントロールを削除する
obj.Delete
End If
Next obj
End With
コードの説明
以上のコードは、本マクロを1度実行して生成されたチェックボックスを削除する処理のコードです。
これから新たにチェックボックスを設置したいので、設置されたチェックボックスは削除しておきます。
コードの詳細
32行目のコードでは、シート「data」上のシェイプ・コントロールの数分だけ処理を繰り返すFor文を用意し、その中で「test_cbx」の文字列が含まれた名前のチェックボックスだけを削除します。
なお、今回のマクロで設置するチェックボックスには、「test_cbx」という文字列が含まれた名前を付けています。(147行目)
'チェックボックスに名前を付ける(重複しないよう項番も付与する)
cb.Name = "test_cbx" & cnt
「test_cbx」という文字列を含めることで、チェックボックスを削除するのに特定がしやすくなります。(他のコントロールは削除しないようにする)
削除するチェックボックスを特定するために、34行目でInStr関数を使ってコントロールに「test_cbx」の文字列が含まれているか判定します。
「test_cbx」の文字列が含まれている場合は、39行目でそのコントロールを削除します。
注目すべきコード④
次に見て頂きたいのは48行目から56行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set adoCON = CreateObject("ADODB.Connection")
'データベース接続情報の取得
adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" _
& DbName & ""
'Accessに接続する
adoCON.Open
コードの説明
以上のコードは、マクロがAccessのDBに接続するための接続情報を取得して接続するコードです。
コードの詳細
48行目のコードでは、Connectionインスタンスを生成します。
このインスタンスがないとマクロがAccessのDBに接続することができないので必ず生成しておきます。
51行目から53行目で接続情報を取得し、56行目でOpenメソッドを実行してマクロがAccessのDBに接続します。
注目すべきコード⑤
次に見て頂きたいのは59行目から70行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set adoRS = CreateObject("ADODB.Recordset")
'クライアントサイドカーソルに変更
adoRS.CursorLocation = adUseClient
'先ほど取得したデータベース接続情報が設定されているadoCONをActiveConnectionプロパティに設定し、
'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
'⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
adoRS.ActiveConnection = adoCON
'カーソルを開く
adoRS.Open tblNM, adoCON
コードの説明
以上のコードは、Recordsetオブジェクトのインスタンスを生成し、マクロがAccessのDBに接続する処理のコードです。
コードの詳細
59行目のコードでは、Recordsetオブジェクトのインスタンスを生成します。
このインスタンスがないとマクロがAccessのDBのテーブルからデータを取得することができないので必ず生成しておきます。
Recordsetオブジェクトのインスタンスが生成されると、マクロが接続したAccessのDBのテーブルに対してSQLのSELECT文を実行することができます。
62行目でクライアントサイドカーソルに変更し、67行目でConnectionオブジェクトとRecordsetオブジェクトを関連付けます。
70行目でマクロがAccessのDBを開き、テーブルを参照します。
注目すべきコード⑥
次に見て頂きたいのは73行目から99行目です。
'Accessからテーブルデータを取得するSQL文を作成
sqlStr = "SELECT "
For cnt = 0 To adoRS.Fields.Count - 1
Select Case True
Case adoRS.Fields.Item(cnt).Name <> "フラグ"
'フィールドが「フラグ」ではない場合
'列名をSELECT文に使う
sqlStr = sqlStr & adoRS.Fields.Item(cnt).Name & ","
'フィールド名をシートに出力する
Worksheets("data").Cells(bgnRPos - 1, cnt + 2).Value = adoRS.Fields.Item(cnt).Name
Case Else
End Select
Next
'末尾の「'」を削除
sqlStr = left(sqlStr, Len(sqlStr) - 1)
sqlStr = sqlStr & " FROM " & tblNM
sqlStr = sqlStr & " ORDER BY 社員番号 ASC"
コードの説明
以上のコードは、Accessからテーブルデータを取得するSQL文を作成する処理のコードです。
コードの詳細
73行目のコードは、SELECT文の「SELECT(句)」です。
75行目のコードは、参照するテーブルのフィールド数分処理を繰り返すForです。
84行目のコードで、参照するテーブルのフィールド名を取得してSELECT文を組み立てます。
なお、取得したフィールド名は「,」(カンマ)区切りでSELECT文を組み立てていきます。
87行目ではSELECT文の組み立ての処理とは関係ないですが、ここまででフィールド名が取得できたので、シートにフィールド名を出力します。
また、取得したフィールド名は「,」(カンマ)区切りでSELECT文を組み立てていきますが、最後のフィールド名にも後ろに「,」が付いているので、その「,」を除去します。(96行目)
98行目のコードではFROM句を、99行目のコードではORDER BY句をつなげてSELECT文を組み立てます。
以上でSELECT文を組み立てることができました。
注目すべきコード⑦
次に見て頂きたいのは102行目です。
'フィールド名が取得できたので一度レコードセットを閉じる
adoRS.Close
以上のコードは、Recordsetを閉じる処理のコードです。
ここまでで開いたRecordsetはフィールド名を取得するためであり、この先は組み立てたSELECT文を実行するのにまたRecordsetを開く必要があります。
ただしそのままSELECT文を実行するのにRecordsetを開こうとすると、すでにRecordsetが開かれているとエラーになってしまいます。
なのでここで1度Recordsetを閉じておきます。
注目すべきコード⑧
次に見て頂きたいのは105行目から108行目です。
'SELECT文を実行して、Recordsetオブジェクトにデータをロード
adoRS.Open sqlStr, adoCON
'recordsetの値をシートのセルに出力する
Worksheets("data").Range("B" & bgnRPos).CopyFromRecordset adoRS
コードの説明
以上のコードは、「注目すべきコード⑥」で組み立てたSELECT文を実行し、取得した値をシートのセルに出力する処理のコードです。
CopyFromRecordsetプロパティに、recordset用変数「adoRS」を指定することで、SELECT文で抽出したデータをExcelのシートのセルに出力することができます。
注目すべきコード⑨
次に見て頂きたいのは114行目から118行目です。
'Accessから、チェックボックスの付与を行うのに必要な判断情報を取得するSQL文を作成
sqlStr = "SELECT 社員番号, フラグ FROM " & tblNM
sqlStr = sqlStr & " ORDER BY 社員番号 ASC"
'SELECT文を実行して、Recordsetオブジェクトにデータをロード
adoRS.Open sqlStr, adoCON
コードの説明
以上のコードは、フィールド「フラグ」の値を取得するSELECT文を組み立てて実行する処理のコードです。
フィールド「フラグ」の値は、チェックボックスにチェックを付けるかどうかを判定するのに使います。
チェックボックスにチェックを付けるかどうか判定しているコードは146行目で行ってます。
If adoRS.Fields("フラグ").Value = 1 Then
注目すべきコード⑩
次に見て頂きたいのは124行目です。
'チェックボックスを設置するセルの範囲を取得する
Set rng = Worksheets("data").Range("A" & bgnRPos & ":A" & CStr(bgnRPos + adoRS.RecordCount - 1))
コードの説明
以上のコードは、チェックボックスを配置するセルの範囲を取得するコードです。
注目すべきコード⑪
次に見て頂きたいのは126行目です。
For Each cell In rng
コードの説明
以上のコードは、「注目すべきコード⑩」で説明したチェックボックスを配置する範囲のセルを一つ一つ繰り返し取得するFor文のコードです。
セルを取得する目的はセルの上端位置と左端位置が欲しいためです。
上端位置と左端位置については詳しくは「注目すべきコード⑫」でご説明します。
注目すべきコード⑫
次に見て頂きたいのは132行目から135行目です。
'チェックボックスの上端位置を取得する(セルの縦位置の中央に設置)
'→(セルのHeightの中央値に設定し、さらに微調整分マイナスする)
top = cell.top + (cell.Height / 2) - 9
'チェックボックスの左端位置を取得する
leftPos = cell.left
コードの説明
以上のコードは、セルの上端位置と左端位置を取得し、その位置からチェックボックスを設置するチェックボックスの上端位置と左端位置を決めます。
今回のサンプルでは、チェックボックスの上端位置は、配置するセルの上端から真ん中に設定しています。
また、チェックボックスの左端位置は、配置するセルの左端位置と同じにしています。
注目すべきコード⑬
次に見て頂きたいのは138行目です。
'チェックボックスを生成して配置する
Set cb = Worksheets("data").CheckBoxes.Add(left, top, 0, 0)
以上のコードは、セルにフォームコントロールのチェックボックスを配置している処理のコードです。
CheckBoxes.Addメソッドに左端位置、上端位置、横幅、縦幅の4つの値を引数に設置して実行することでセルにフォームコントロールのチェックボックスが配置されます。
今回のサンプルでは横幅、縦幅には0を設定しています(チェックボックスの□だけが欲しいので(チェックボックスの文言部分は使わない))
注目すべきコード⑭
次に見て頂きたいのは141行目から144行目です。
'チェックボックスのキャプションを空にする
cb.Caption = ""
'チェックボックスに名前を付ける(重複しないよう項番も付与する)
cb.Name = "cbx" & cnt
コードの説明
以上のコードは、配置されたチェックボックスのキャプションと名前の設定を行っている処理のコードです。
コードの詳細
141行目では、チェックボックスのキャプションを空にしています。
チェックボックスが配置された直後は、デフォルトでキャプションが設定されており表示されます。
今回のサンプルでは□だけの表示にしたいのでキャプションは何も設定しないようCaptionプロパティにブランクを設定しています。
144行目では、チェックボックスに名前を付けています。
今回のサンプルでは複数設置しているので、フォームコントロールのチェックボックスの名前が重複しないよう「test_cbx」という名前にカウンタの値をつなげて名前を一意にしています。
注目すべきコード⑮
次に見て頂きたいのは146行目から153行目です。
If adoRS.Fields("フラグ").Value = 1 Then
'「フラグ」という名前のフィールドの値が1の場合
' チェックボックスにチェックを付ける
cb.Value = xlOn
End If
コードの説明
以上のコードは、フィールド「フラグ」の値を判定し、合致する場合はチェックボックスにチェックを付ける処理のコードです。
フィールド「フラグ」の値が1の場合はチェックボックスにチェックを付けます。
フィールド「フラグ」の値が1出ない場合はチェックボックスにはチェックを付けません。
コードの詳細
146行目のコードでは、フィールド「フラグ」の値が1かどうか判定し、1の場合は151行目でチェックボックスにチェックを付けます。
フィールド「フラグ」の値が1以外の場合は何もしません。
動作確認
今回は以下のExcelファイルを用意しました。
また、今回用意したAccessのDBのテーブルは下の通りです。
マクロ実行後
マクロを実行すると下の通りに出力されます。
マクロがAccessのDBにある「T_社員」テーブルのデータを取得してシート「data」の表に出力されました。
また、A列にチェックボックスをマクロが生成し、フィールド「フラグ」の値が1の場合はチェックボックスにチェックを付けられました。
0の場合はチェックが付いていません。
最後に
この記事では、Excelのシートにデータ件数分のチェックボックスを追加してAccessのDBの値で制御する方法についてご説明しました。
AccessのDBのテーブルにあるデータを表示させたい場合、そしてAccessのDBのテーブルにあるフィールドの値によってシート上のテキストボックスにチェックを付けるか付けないか制御して表示させたい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
