EXCELのワークシートに対してSQL文で問い合わせを行いデータを取得する方法(VBA)

EXCELでSQLサンプル

こんな感じのEXCELのワークシートに対してSQL文を発行し、結果を取得する方法です。
１行目をフィールド名と見立てて、Where句でデータを絞り込んだり、ソートしたりすることが可能です。

以下のサンプルコードでは自ワークブック(*.xls,*.xlsm等)に対しADOでEXCELファイルをオープンし、SELECT文を発行しています。
実際に動くEXCEL VBAのサンプルプログラムは一番下にあります。

ここで書いているSQLはANSI-92 SQL相当なので、以下のことが可能です。

• WHERE句、LIKE等も使用可
• ORDER BY
• GROUP BYでSUMやCOUNTなどが使える
• 複数の表をJOINで結合
• サブクエリを含める
• １つのシート上の特定範囲を複数のテーブルとして結合

VBAコード例

Public Sub MyADOExcel()
Const adOpenKeyset = 1
Const adLockReadOnly = 1

Dim cn As Object
Dim rs As Object
Dim strSQL As String

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
'1行目がヘッダの場合はHDR=YESにする。NOの場合はF1,F2,F3・・・と番号が振られる。

cn.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"

cn.Open ThisWorkbook.FullName '自ワークブックのファイル名を指定しているが、
'もちろん別ワークブックでもOK！
'cn.Open "c:\tmp\sample.xls" 等とする

strSQL = ""
strSQL = strSQL & " SELECT F1,F2,F3,F4,F5 "
strSQL = strSQL & " FROM [Sheet2$A3:Z1000] " 'Sheet2のA3:Z100にデータがあると仮定する。
strSQL = strSQL & " WHERE F1 IS NOT NULL " '単にCells(1,1)からシート全体を取得したい場合は単に[Sheet2$]とすればいい。
strSQL = strSQL & " ORDER BY F2 " '（最後の$は必要)
rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly

Sheet1.Cells(10, 3).CopyFromRecordset rs 'シート1のセル(10,3)からデータをセットする

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

マクロ実行中に自ワークシートを書き換え、そのワークシートにSQL文を発行したいとき

１つのEXCELワークブック上に、ユーザーインターフェースもデータシートもマクロも乗せて、マクロ実行中に自ワークシートを書き換え、そのワークシートにSQL文を発行したいとき、この方法だと保存してあるEXCELファイルにしかSQLでデータ取得できないので一旦保存する必要があります。
しかし作業中のEXCELファイルをまだ上書き保存したくないと言う事があるかもしれません。
その場合は、ActiveWorkbook.SaveCopyAs文で一旦別ファイルに保存し、そのファイルをオープンしてSQL発行するようにすると実行中のEXCELに対して影響なく続けることが出来ます。

EXCELの自ワークシートに対してSQL問い合わせするサンプル

会員情報シートを検索するサンプルを作成しました。
いわゆる顧客台帳検索システムです。
5000件のデータから検索し、結果をシートに表示します。

使い方

とりあえず都道府県、血液型、性別のどれか１つを選択して、検索実行を押下してみて下さい。

EXCELでSQLサンプルプログラム　ダウンロード

[download id=”3268″]

セキュリティの警告が出ると思うので、「マクロを有効にする」を選択して下さい。