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

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演算子で結合
  • サブクエリを使用して、クエリの内部で別のクエリを実行
  • 特定の範囲を複数のテーブルとして結合

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つを選択し、検索実行ボタンをクリックしてください。

image

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

“ExcelSqlSample.xls” をダウンロード

ExcelSqlSample.xls – 13254 回のダウンロード – 2.22 MB

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

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

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

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

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

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

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

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

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

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

ダウンロード

“ExcelSqlSample.xls” をダウンロード

ExcelSqlSample.xls – 13254 回のダウンロード – 2.22 MB

参考資料

著:たてばやし 淳
¥2,168 (2022/10/18 14:35時点 | Amazon調べ)
著:高橋 麻奈
¥800 (2022/10/18 14:38時点 | Amazon調べ)

コメント

  1. 河村 茂樹 より:

    Win10 エクセル2010では動きます。Win8.1エクセル2013では オブジェクト ‘可変長の名前付きセル範囲’ が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください とエラーが出る どうしてでしょうか?
    エラーになる原因、対策を教えて下さい。

  2. IT土方 より:

    Windows 10+Excel 2016で確認しましたが問題なく動作しました。

    何処で落ちているのか、わかれば何らかの対処方法があると思いますが、
    コメントで頂いた情報だけではこれ以上の事はわかりません。

タイトルとURLをコピーしました