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
(既定値)
1Recordsetオブジェクト内を末尾方向に向かって検索します。
条件を満たすデータが見つからない場合は
カーソルの位置は末尾のレコードの後の位置に移動します。
adSearchBackward-1Recordsetオブジェクト内を先頭方向に向かって検索します。
条件を満たすデータが見つからない場合は
カーソルの位置は先頭のレコードの前の位置に移動します。

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


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

コード例
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

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

コードの解説

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で外部データベースの条件を満たすレコードを抽出する方法に
ついての解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク