やっぱ、二次元の表で定義される色々なRDBに接続できるとは、たかがVBA、されどVBAだぜ － ADO・ActiveX Data Object （エクセルVBA）

      
エクセルVBAに、Access、SQLなどを使いこなすライブラリーがあります。
もちろん、プログラムを組む必要があるのですが、活用できれば凄いんだ。

以前の投稿でも、ベタなプログラムで検索に要した時間を紹介しました。
しかも、エクセルの表示行数をはるかに超えたデータ量のCSVファイルね。

データベースは、検索した結果を見るのが、活用の基本中の基本です。
もちろんデータの追加、訂正もありますが、どれだけ高速に検索するかだ。

この点でSQLが優れているのは、ISOで国際規格にまとめられている事。
オラクルやMySQLなど、各社データベースエンジンでも互換性があるの。

だから、SQLのコマンドをVBAでかじっておけば、、使い勝手はあるはず。
なので、前回の投稿は、基本的なSQLの検索コマンドを仕込みました。

もっとも、実用性では、検索語や検索条件を随時変える必要も出ます。
しかも、セルやをユーザーフォームの入力値を変数扱いで投入したいな。

汎用検索ソフト（csvファイル専用）

後は、実行ボタンを作っておいて起動させるとワークシートに表示される。
そんな手順を考えて検索実行のコードを書いたけど、エラーが出ました。

それが冒頭の表示なのですが、先ず、、イミディエイトウィンドウでチェック。
そうすると、変数を挟み込んで書き込んだコードは、約束を守っているの。

でも、初期化文字列の形式は、OLE DB仕様に準拠していない云々。
うーん、SQLの検索構文は、”SELECT　FROM　Where ="が原形。

この間に、変数として、ファイル名やら検索語などをはめ込んで行く分け。
それを＆～&でつなぎますが、何かいやなのは、先頭のダブルクォートだ。

末尾にも同じに付けなければならず、全体でSQLを引用した意味かな。
なので、最初と最後尾の二重引用符を表示させるため、””””と打った。

以前、こういった二重引用符はブラウザーを起動させるために使用済み。
この時は、Shellコマンドのためにパス付きで実行コマンドを指定しました。

Dim iePath As string
iePath = """C:\Progrm Files\Internet Explorer\ieexplorer.exe"""
Shell (iePath & 表示したいURL)

なので、何となく使い方はわかっていたんだけど、SQL構文だと少し変だ。
だって、最初と最後で二重引用符を四連発しないと、エラーになります。

まあ、三連発で打つと朱字で警告表示されて、訂正せざるを得ません。
でも、実行すれば冒頭のエラーがいつも出るばかりで糞詰まってしまった。

      
煮詰まってしまい、構文をどう書くか悩んでいたら、ネットに味方が降臨。
”エクセルの神髄　鵜原パソコンソフト研究所”さんが、ヒントをくれました。

それは、ずばり”ADO(ActiveX Data Objects)の使い方の要点”だ。
この参考になるコードを拝見したところ、なるほど工夫が見られました。

　　Dim strSQL As String
　　strSQL = ""
　　strSQL = strSQL & " SELECT *"
　　strSQL = strSQL & " FROM"
　　strSQL = strSQL & " CSVTEST.csv"
      ※シングルクォーテーションを2個重ねるのが新機軸

要するに、SQL実行の構文記述に、変数の積上げ算をするアイデアだ。
強引に＆～&でつなげばエラーが出るのかもしれず、早速に応用します。

すると、あーら不思議と検索できてしまいましたが、今度は演算子だよ。
”Like”にはワイルドカードが必要で、検索のために足し忘れていたんだ。

”*hogehoge*"なんだけど、*マークはエラーで、％記号が正解でした。
この他、文字列の検索語は、’hogehoge’と括るけれど、数値は違う。

そのまま加えるだけなので、条件式で文字列か数値か見分けないとな。
なので、ISNumeric関数を使ったけど、変数がBooleanなのも独特。

   Dim NumTxt  As Boolean
   NumTxt = IsNumeric(UserForm1.TextBox1.Text)
   Select Case NumTxt
   Case True
   strSQL = strSQL & " "
   strSQL = strSQL & Val(SearchForm.TextBox1.Text)
   Case False
   strSQL = strSQL & " '"
   strSQL = strSQL & SearchForm.TextBox1.Text
   strSQL = strSQL & "'"
   End Select

Select Case で選別させていますが、変数も真/偽で判定しています。
というわけで、興味がおありならファイルはここからダウンロードして下さい。

バイナリー形式のxlsbファイルにしてありますので、グーグルドライブの挙動に邪魔されずにダウンロードできるとは思いますが、このプログラムで百五十万行のデータを五つの条件指定で検索しても、一分以内とさほど時間がかからなかったし、検索結果がエクセルの最大行数で表示できる限り、この汎用検索ソフトは、かなり役立つのではないかと思ったのでした。

いいねと思ったら、三つポチっとね！
  
にほんブログ村    にほんブログ村      にほんブログ村