Excel VBA ADOで外部データベースの条件を満たすレコードを抽出する

スポンサーリンク

外部データベースの条件を満たすレコードを抽出する方法

外部データベースのテーブルのレコードが格納されたRecordset【レコードセット】オブジェクト内で特定の条件を満たすレコードを検索するには、Recordset【レコードセット】オブジェクト
Find【ファインド】メソッドを使用します。

条件を満たすレコードが見つかった場合は、そのデータを含むレコードにカーソルが移動します。

Recordset【レコードセット】オブジェクト内では、カーソルがあるレコード(カレントレコード)が操作対象になります。

Recordset.Find【レコードセットファインド】メソッド

Recordset.Find【レコードセットファインド】メソッドの書式と設定値(引数)の説明

[]内は省略可能です。
オブジェクト変数.Find( Criteria[, SearchDirection] )

  • オブジェクト変数(必須)
    Recordset【レコードセット】オブジェクトのインスタンスが格納されているオブジェクト変数を指定します。
  • Criteria【クライテリア】(必須)
    Recordset【レコードセット】オブジェクト内を検索する条件式を指定します。検索条件は「検索するフィールド名と条件とする文字列」を指定します。条件とする文字列は「’」シングルクォーテーションで囲み検索条件は「”」ダブルクォーテーションで囲みます。
    【(例)Criteria:=”商品名= ‘B商品'” 】
    Find【ファインド】メソッドでの検索は1つのフィールドによる条件式を指定できます。複数のフィールドによる条件式を指定する場合はFilter【フィルター】プロパティを使用します。
  • SearchDirection【サーチディレクション】(省略可)
    Recordset【レコードセット】オブジェクト内での検索方向をSearchDirectionEnum列挙型の定数で指定します。
SearchDirectionEnum列挙型の定数
定数 内容
adSearchForward
(既定値)
1 Recordsetオブジェクト内を末尾方向に向かって検索します。
条件を満たすデータが見つからない場合は
カーソルの位置は末尾のレコードの後の位置に移動します。
adSearchBackward -1 Recordsetオブジェクト内を先頭方向に向かって検索します。
条件を満たすデータが見つからない場合は
カーソルの位置は先頭のレコードの前の位置に移動します。

※Find【ファインド】メソッドを実行するとき、外部データベースの
どのレコードにもカーソルがない(カレントレコードがない)場合
エラーが発生しますので実行前にMoveメソッドでカーソルを移動します。


Accessデータベースの商品マスターテーブルの
商品フィールドのC商品を検索して
レコードのデータをワークシートに抽出する例

コードと解説

ADOのライブラリファイルへの参照設定がされていることが前提のコード例です。

Sub 特定レコードの検索()
Dim コネクション As New ADODB.Connection
Dim レコード As New ADODB.Recordset
Dim i As Long
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
レコード.Open Source:="商品マスター", ActiveConnection:=コネクション, _
CursorType:=adOpenStatic
With レコード
i = 1
Do
.Find Criteria:="商品名 = 'C商品'"
If .EOF Then
Exit Sub
Else
Cells(i, 1).NumberFormatLocal = "@"
Cells(i, 1).Value = .Fields("商品コード").Value
Cells(i, 2).Value = .Fields("商品名").Value
Cells(i, 3).NumberFormatLocal = "#,##0"
Cells(i, 3).Value = .Fields("仕入れ単価").Value
Cells(i, 4).NumberFormatLocal = "#,##0"
Cells(i, 4).Value = .Fields("販売単価").Value
i = i + 1
.MoveNext
End If
Loop
End With
レコード.Close: Set レコード = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub
2行目【Dim コネクション As New ADODB.Connection】
Dim【デム】ステートメントでNew【ニュー】キーワード使用して外部データベースとの
接続を確立するConnection【コネクション】オブジェクトのインスタンスを生成してオブジェクト変数「コネクション」に格納します。


3行目【Dim レコード As New ADODB.Recordset】
Dim【デム】ステートメントでNew【ニュー】キーワード使用して外部データベースのレコードを参照するRecordset【レコードセット】オブジェクトのインスタンスを生成してオブジェクト変数「レコード」に格納します。


4行目【Dim i As Long】
繰り返し処理の中でワークシートの行番号を格納する変数iを長整数型(Long)で宣言します。


5行目~7行目
【コネクション.Open ConnectionString:= _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=C:¥Users¥Desktop¥Database1.accdb;”】
Connection【コネクション】オブジェクトのOpen【オープン】メソッドを使用して外部データベースの接続をします。


8行目9行目【レコード.Open Source:=”商品マスター”, ActiveConnection:=コネクション, _
CursorType:=adOpenStatic】

Recordset【レコードセット】オブジェクトのOpen【オープン】メソッドを使用して外部データベースのレコードを参照します。
引数CursorType【カーソルタイプ】をデータの検索やレポートを作成するときに指定するadOpenStatic【スターティック】で静的タイプに指定します。


10行目【With レコード】
外部データベースのレコードを表すRecordset【レコードセット】オブジェクトのインスタンス(複製)が格納されているオブジェクト変数「レコード」をWith【ウィズ】ステートメントで指定します。


11行目【i = 1】
繰り返し処理のDo Loop【ドゥループ】ステートメントで使用するセルの行番号を格納する変数「i」に最初の行番号の1行目を表す1を代入します。


12行目【Do】
Do Loop【ドゥループ】ステートメントで繰り返し処理を宣言します。繰り返し終了の条件はここでは指定せず以下のIf【イフ】ステートメント(条件分岐)で繰り返し条件を指定します。


13行目【.Find Criteria:=”商品名 = ‘C商品'”】
With【ウィズ】ステートメントで指定したRecordset【レコードセット】オブジェクトのインスタンスが格納されているオブジェクト変数「レコード」に対してFind【ファインド】メソッドを使用して検索を実行します。条件式は商品名フィールドのC商品レコードです。


14行目【If .EOF Then】
If【イフ】ステートメント(条件分岐)で、EOF【エンドオブファイル】がTrueのときを定義します。つまり、カレントレコード(カーソルの位置)が最後のレコードより後にある場合を定義します。


15行目【Exit Sub】
14行目の条件分岐が成立した場合、つまり、カレントレコードが最後のレコードを超えた場合、Exitステートメントで
Subプロシージャーを終了します。(処理を終了します。)


16行目【Else】
Else【エルズ】ステートメントで以下に条件が成立しなかった場合つまり、カレントレコード(カーソル)がデータのレコード内にある場合の処理を実行します。


17行目【Cells(i, 1).NumberFormatLocal = “@”】
1列目のセルには文字列型の商品コードが代入されますが、商品コードは数値で表されているためExcelが自動でセルの表示形式を数値に変換してしまい商品コードの先頭の00が表記されないのでNumberFormatLocalプロパティを使用してセルの表示形式を文字列に強制的に指定します。


18行目【Cells(i, 1).Value = .Fields(“商品コード”).Value】
Find【ファインド】メソッドで検索されたC商品のレコードにあるカーソルの「商品コート」フィールドの値をValueプロパティで取得して1列目のi行目のセルに値を代入します。


19行目【Cells(i, 2).Value = .Fields(“商品名”).Value】
Find【ファインド】メソッドで検索されたC商品のレコードにあるカーソルの「商品名」フィールドの値をValueプロパティで取得して2列目のi行目のセルに値を代入します。


20行目【Cells(i, 3).NumberFormatLocal = “#,##0″】
3列目のセルに代入する「仕入れ単価」は通貨型になっていてセルに代入した場合「¥400.00」と小数点以下2桁まで
表示されてしまうのでNumberFormatLocalプロパティを使用して桁区切りの数値型にセルの表示形式を強制的に変更します。


21行目【Cells(i, 3).Value = .Fields(“仕入れ単価”).Value】
Find【ファインド】メソッドで検索されたC商品のレコードにあるカーソルの「仕入れ単価」フィールドの値をValue【バリュー】プロパティで取得して、3列目のi行目のセルに値を代入します。


22行目【Cells(i, 4).NumberFormatLocal = “#,##0″】
4列目のセルに代入する「販売単価」は通貨型になっていて、セルに代入した場合「¥400.00」と小数点以下2桁まで表示されてしまうのでNumberFormatLocalプロパティを使用して桁区切りの数値型にセルの表示形式を強制的に変更します。


23行目【Cells(i, 4).Value = .Fields(“販売単価”).Value】
Find【ファインド】メソッドで検索されたC商品のレコードにあるカーソルの「販売単価」フィールドの値をValue【バリュー】プロパティで取得して4列目のi行目のセルに値を代入します。


24行目【i = i + 1】
セルの行番号を格納する変数iに1を加算して次の行のセルに移動します。


25行目【.MoveNext】
MoveNext【ムーブネクスト】メソッドを使用して外部データベースのカレントレコードを次のレコードに移します。


27行目【Loop】
ここまでの処理を14行目のIf【イフ】ステートメント(条件分岐)の条件が成立するまでつまり、カレントレコード(カーソル)がデータ範囲内の間繰り返し処理をします。


29行目【レコード.Close: Set レコード = Nothing】
Recordset【レコードセット】オブジェクトのClose【クローズ】メソッドでレコードの参照を切断し、Nothing【ナッシング】を代入して
メモリー領域を解放します。


30行目【コネクション.Close: Set コネクション= Nothing】
Connection【コネクション】オブジェクトのClose【クローズ】メソッドで外部データベースの接続を切断し、Nothing【ナッシング】を代入してメモリー領域を解放します。


実行結果


以上で、ADOで外部データベースの条件を満たすレコードを抽出する方法についての解説を終了します。
ありがとうございました。

スポンサーリンク

関連記事・広告