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・・・と番号が振られる。
    'ヘッダに日本語やカッコが含まれていると誤動作の原因になるので損場合は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ファイルを使用する必要があるため、保存する必要があります。

しかし、作業中のExcelファイルを上書き保存したくない場合があるかもしれません。

この場合、ActiveWorkbook.SaveCopyAs文を使用して、一時ファイルに別名で保存し、そのファイルを開いてSQL文を発行することができます。

これにより、実行中のExcelファイルに影響を与えることなく、作業を続けることができます。

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

顧客台帳検索システムのサンプルを作成しました。このシステムは、会員情報を格納しているシートからデータを検索し、結果を別のシートに表示します。

このシステムでは、5000件のデータから検索を行うことができます。

使い方

都道府県、血液型、性別のうち、いずれか1つを選択し、検索実行ボタンをクリックしてください。

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

セキュリティ警告が表示される場合がありますので、「編集を有効にする」を選択してください。

別のセキュリティの警告が表示されたときは

最近のWindowsはセキュリティが厳しくなってきているため、ダウンロードしたマクロ付きのExcelファイルを開いてもすぐにマクロを実行することはできません。

「セキュリティリスク　このファイルのソースが信頼できないため、microsoftによりマクロの実行がブロックされました」

と表示されてしまう場合はいったんExcelファイルを閉じます。

エクスプローラーでダウンロードしたExcelファイルを右クリックしてプロパティを確認してください。
セキュリティのところの「許可する」にチェックを付ける必要があります。

再度ExcelSqlSample.xlsを開くとExcel側でも警告を受けますので「コンテンツの有効化」を選択します。

古いシステムの場合、以下のセキュリティの警告が出かもしれません。
「マクロを有効にする」を選択して下さい。

ここまでの手順を踏んでようやくネットからダウンロードしたマクロ付きのExcelファイルを正しく開くことができます。

簡易顧客台帳としても使えると思います。

ダウンロード

参考資料

Excel マクロ＆VBA　［実践ビジネス入門講座］【完全版】　「マクロの基本」から「処理の自動化」まで使えるスキルが学べる本気の授業

著:国本 温子

¥1,564 （2022/10/18 14:33時点 | Amazon調べ）

Amazon

ポチップ

Excel VBA塾: 初心者OK！　仕事をマクロで自動化する12のレッスン

著:たてばやし 淳

¥2,168 （2022/10/18 14:35時点 | Amazon調べ）

Amazon

ポチップ

Excel マクロ＆VBA やさしい教科書 ［2021／2019／2016／Microsoft 365対応］ (一冊に凝縮)

著:古川 順平

¥1,980 （2022/10/18 14:36時点 | Amazon調べ）

Amazon

ポチップ

よくわかるSQL

著:高橋 麻奈

¥800 （2022/10/18 14:38時点 | Amazon調べ）

Amazon

ポチップ