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・・・と番号が振られる。
'ヘッダに日本語やカッコが含まれていると誤動作の原因になるので損場合はNOにしたほうが無難
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 [Sheet1$A3:Z1000] " 'Sheet1のA2: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サンプルプログラム ダウンロード
“ExcelSqlSample.xls” をダウンロード
ExcelSqlSample.xls – 9118 回のダウンロード – 2.22 MBセキュリティの警告が表示される思うので「編集を有効にする」を選択してください。
別のセキュリティの警告が表示されたときは
最近のWindowsはセキュリティが厳しくなってきているため、ダウンロードしたマクロ付きのExcelファイルを開いてもすぐにマクロを実行することはできません。
「セキュリティリスク このファイルのソースが信頼できないため、microsoftによりマクロの実行がブロックされました」
と表示されてしまう場合はいったんExcelファイルを閉じます。
エクスプローラーでダウンロードしたExcelファイルを右クリックしてプロパティを確認してください。
セキュリティのところの「許可する」にチェックを付ける必要があります。
再度ExcelSqlSample.xlsを開くとExcel側でも警告を受けますので「コンテンツの有効化」を選択します。
古いシステムの場合、以下のセキュリティの警告が出かもしれません。
「マクロを有効にする」を選択して下さい。
ここまでの手順を踏んでようやくネットからダウンロードしたマクロ付きのExcelファイルを正しく開くことができます。
簡易顧客台帳としても使えると思います。
ダウンロード
“ExcelSqlSample.xls” をダウンロード
ExcelSqlSample.xls – 9118 回のダウンロード – 2.22 MB参考資料
ポチップ
ポチップ
コメント
Win10 エクセル2010では動きます。Win8.1エクセル2013では オブジェクト ‘可変長の名前付きセル範囲’ が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください とエラーが出る どうしてでしょうか？
エラーになる原因、対策を教えて下さい。
Windows 10+Excel 2016で確認しましたが問題なく動作しました。
何処で落ちているのか、わかれば何らかの対処方法があると思いますが、
コメントで頂いた情報だけではこれ以上の事はわかりません。