ExcelVBA-Excelファイルをデータベースとして使う-その3です。 前回の記事はこちらになります。 hirocom777.hatenadiary.org
さて、データベースらしくSQLを使ったデータの検索を説明したいと思います。
Microsoft ActiveX Data Objects 2.8 Libraryを参照設定して試してみてください。 ソースリストは此方です。
Public Function SelectRecord(strFileName As String, strSQL As String, Optional rngRange As Range = Nothing) As Long On Error GoTo HrrorHandler Dim adoCn As Object Set adoCn = CreateObject("ADODB.Connection") With adoCn .Provider = "Microsoft.ACE.OLEDB.12.0" .Properties("Extended Properties") = "Excel 12.0" .Open strFileName End With Dim adoRs As Object Set adoRs = CreateObject("ADODB.Recordset") adoRs.CursorLocation = adUseClient adoRs.Open strSQL, adoCn If Not rngRange Is Nothing Then rngRange.CopyFromRecordset adoRs SelectRecord = adoRs.RecordCount adoRs.Close adoCn.Close HrrorHandler: If Err.Number <> 0 Then MsgBox "データベース接続に失敗しました" End Function
まずは、簡単に動かしてみましょう前回の名簿データベースにデータを追加してみました。
Private Sub SelectData() Const SQL As String = "SELECT * FROM [Sheet1$] WHERE 性別 = '男性'" MsgBox SelectRecord(ThisWorkbook.Path & "\データ.xlsx", SQL, Sheet1.Range("A1")) & "件見つかりました" End Sub
このプロシージャSelectDataを実行すると以下のレスポンスが返ってくるはずです。
そしてSheet1には以下のように表示されます。
男性のデータのみ2件に絞って表示されました。
次に、プロシージャSelectRecordのポイントを解説します。
引数として使用する変数は以下の通りです。
strFileName データベースのファイル名
strSQL 検索に使用するSQLの文字
rngRange 結果を表示するセルの起点(オプション)
データベースに接続する方法の基本は前回のプロシージャAddRecordと同じですね。
でも、今回は引数にシートの指定がありません。シートの指定はSQLの文字列内で指定するんです。
シート名の最後に"$"をつけて"[]"で囲んで指定します。
プロシージャSelectRecordは関数です。成功すると、絞ったレコードの数を返します。
また、最後にシートのセルを指定するとセルを起点に絞ったレコードを全部書き出してくれます。
動作の確認に便利です。
SQLの内容を変えて色々試してみると面白いと思います。
次回は、実際に運用するときに僕が気が付いた点について書きたいと思います。
hirocom777.hatenadiary.org
よろしくお願いします!!
ExcelVBA-Excelファイルをデータベースとして使う連載はコチラからどうぞ!!