これまで、ADOなるデータアクセス技術について、投稿を繰り返してきました。
エクセルのファイルまで呼び出せて便利なのですが、VBAプログラムは必須。
なので、プログラムコードをあらかた紹介しましたが、今回が締めくくりです。
まあ、エクセルのファイル自体もデータベースとみなしてＡＤＯ接続するテク。
エクセルだったら、標準で検索機能はありますが、複数条件の操作は無理。
一致する条件以外に、以上以下とか、含むとか、複雑に条件を設定したい。
しかも、データベースにプログラムを書かず、汎用プログラムを作っておく。
これを使えば、どんなエクセルのファイルでも、必要に応じて検索できます。
というのも、ＡＤＯは、データベースのファイルに接続して作業する考え方。
例えばエクセルなら、ファイル名とシート名を指定して接続を呼びかけます。
これができたら、検索する条件を設定して実行するという、手順を踏みます。
つまり、使わなければ接続を切ってしまえばよく独立したプログラムで充分。
ネットで検索するとサンプルコードの記述も見つかりますが、意外と古臭い。
ワークシートのファイル拡張子も”ｘｌｓ”で、”ｘｌｓｘ”の事例が少ないな。
一方、ＶＢＡとＡＤＯ接続の参照ライブラリーバージョンも上がっているんだ。
だから、ネットのコードは参考にはなるけど、自ら手直しをすることになる。
なので、エクセルファイルをＡＤＯで接続するコーディングを載せておきます。
この場合、検索用シートのフォルダーに接続されるファイルがある設定です。
そして、ADO関連の変数をパブリック扱いでて共用させれば、記述が楽です。
※パブリック変数の記述（モジュール先頭に記述）
Public AdoCnnct As New ADODB.Connection
Public AdoRcrdst As New ADODB.Recordset
Public strSQL As String
Public strDb As String
Public Constr As String
Option explicit
Sub SrchCnnct()
strDb = ThisWorkbook.Path & "\Book1.xlsx"
Constr = "Provider=Microsoft.ACE.OLEDB.12.0;"
Constr = Constr & "Data Source=" & strDb
Constr = Constr & ";Extended Properties="
Constr = Constr & """Excel 12.0;HDR=Yes;"""
With AdoCnnct
.ConnectionString = Constr
.Open
End With
'注：Microsoft ActiveX Data Objects 6.1 Libraryを設定しておくこと
End Sub
ここでポイントは、ADＯの接続記述が長くなるので、積上げ算の記述方法。
条件が読みやすくなりますが、末尾のＨＤＲはシート1行目がフィールド名ね。
つまり、一行目が検索項目の記述に使われていることを、意味しています。
もし、検索結果を表示するときにフィールド名も表示させたいのなら一工夫。
この記述ですが、次の検索実行時のコーディングと別に参考で書きました。
この検索の記述も長くはなりますが、分かりやすく積上げ算で分解します。
Sub FieldName()
Dim tbl As String
Dim i As Long
tbl = "Sheet1"
strSQL = "Select * from" & " [" & Tbl & "$]" & ";"
AdoRcrdst.Source = strSQL
AdoRcrdst.ActiveConnection = AdoCnnct
AdoRcrdst.Open
For i = 0 To (AdoRcrdst.Fields.Count - 1)
ActiveSheet.Cells(1, i + 1).Value = AdoRcrdst.Fields(i).Name
Next i
AdoRcrdst.Close
Set AdoRcrdst = Nothing
’フィールドを読むだけなので、一度、接続を切り離す
End Sub
Sub SrchActn()
strSQL = ""
strSQL = strSQL & "SELECT * "
strSQL = strSQL & "FROM "
strSQL = strSQL & "["
strSQL = strSQL & "シート名"
strSQL = strSQL & "$]"
strSQL = strSQL & " where ["
strSQL = strSQL & "検索するフィールド名"
strSQL = strSQL & "] "
strSQL = strSQL & "=" '検索条件
strSQL = strSQL & " "
strSQL = strSQL & " '"
strSQL = strSQL & "検索語"
strSQL = strSQL & "'"
strSQL = strSQL & ";"
strSQL = strSQL & ""
Set AdoRcrdst = AdoCnnct.Execute(strSQL)
Worksheets("Sheet1").Cells(2, 1).CopyFromRecordset AdoRcrdst
AdoRcrdst.Close
Set AdoRcrdst = Nothing
AdoCnnct.Close
Set AdoCnnct = Nothing
End Sub
最終的には、検索を実行した後で、ADO接続の解除を行うコマンドも必要ね。そうしないと、接続が生きたまんまでシステムリソースが解放されませんな。
ただし、メモリから削除するために、必ずNothingを組合わせてセットします。
まあ、検索したければ、毎回アクセスしても、そんなに時間はかかりません。
一方、検索の構文は、VBAのプログラミング構文よりも、かなり冗長な記述。
記述する要素の順番も決まっていたり、書けば長くなるのも分かって来ます。
例えば、検索要素が二個あってそれに一致するデータを取得するとします。
上記の例文は一致する要素が一つだけですが、二個ではANDでつなぐの。
SELECT * FROM [Sheet1$] where Region = 'Japan';
SELECT * FROM [Sheet1$] where Region = 'Asia' AND Country = 'Japan';
SELECT * FROM [Sheet1$] where Country = 'Japan' AND Total_Profit >= 200000 AND Total_Profit <= 500000;
要するにANDでつないでいけば、一致、以下、以上の条件絞り込みが可能。
もし、検索条件が文字列なら、'文字列'で前後をアポストロフィで囲むんだ。
それから、フィールド名にスペースがあっても、置換ができるようにしました。
" [検索するフィールド名] "のように、角括弧でくくるようにしたので大丈夫。
これで、スペース付きフィールド名、検索条件を指定しても検索実行が可能。
以前、スペースをアンダースコアで置換しておくのをアドバイスしていました。
間違いではありませんが、以上のような角括弧付きを施せば、より万全です。
というわけで、ユーザーフォームから検索条件を入力するファイルをオマケ。
下記のダウンロード先から、バイナリーファイルをダウンロードしていただいてお使いいただけますが、VBエディターで内容を見ていただくとお分かりの通り、検索語を文字列なのか数値なのかで判断しながら、指定した検索条件に見合ったコーディングを選択して、検索条件式を合成するような、それなりに工夫したプログラムとなっておりますので、改善できるようなアイデアがあれば、ぜひ進言してもらいたいと思う自分がいるのでした。
※ファイル名：UsrFrm_ClckSQLSrchXlsx.xlsm
ここからダウンロードして下さい。
0 件のコメント:
コメントを投稿