EXCELでSQLサンプル

こんな感じのEXCELのワークシートに対してSQL文を発行し、結果を取得する方法です。
1行目をフィールド名と見立てて、Where句でデータを絞り込んだり、ソートしたりすることが可能です。
以下のサンプルコードでは自ワークブック(*.xls,*.xlsm等)に対しADOでEXCELファイルをオープンし、SELECT文を発行しています。
実際に動くEXCEL VBAのサンプルプログラムは一番下にあります。
ここで書いているSQLはANSI-92 SQL相当なので、以下のことが可能です。
- WHERE句、LIKE等も使用可
- ORDER BY
- GROUP BYでSUMやCOUNTなどが使える
- 複数の表をJOINで結合
- サブクエリを含める
- 1つのシート上の特定範囲を複数のテーブルとして結合
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文を発行したいとき
1つのEXCELワークブック上に、ユーザーインターフェースもデータシートもマクロも乗せて、マクロ実行中に自ワークシートを書き換え、そのワークシートにSQL文を発行したいとき、この方法だと保存してあるEXCELファイルにしかSQLでデータ取得できないので一旦保存する必要があります。
しかし作業中のEXCELファイルをまだ上書き保存したくないと言う事があるかもしれません。
その場合は、ActiveWorkbook.SaveCopyAs文で一旦別ファイルに保存し、そのファイルをオープンしてSQL発行するようにすると実行中のEXCELに対して影響なく続けることが出来ます。
EXCELの自ワークシートに対してSQL問い合わせするサンプル
会員情報シートを検索するサンプルを作成しました。
いわゆる顧客台帳検索システムです。
5000件のデータから検索し、結果をシートに表示します。
使い方
とりあえず都道府県、血液型、性別のどれか1つを選択して、検索実行を押下してみて下さい。

EXCELでSQLサンプルプログラム ダウンロード
[download id=”3268″]
セキュリティの警告が出ると思うので、「マクロを有効にする」を選択して下さい。
コメント
Win10 エクセル2010では動きます。Win8.1エクセル2013では オブジェクト ‘可変長の名前付きセル範囲’ が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください とエラーが出る どうしてでしょうか?
エラーになる原因、対策を教えて下さい。
Windows 10+Excel 2016で確認しましたが問題なく動作しました。
何処で落ちているのか、わかれば何らかの対処方法があると思いますが、
コメントで頂いた情報だけではこれ以上の事はわかりません。