Excel を SQL 文で集計する

エクセル関数だととても複雑になってしまう処理をSQLで代替できれば可読性良くあっさりと書けそうね、ということでやり方を調べてみた。

まずは接続するところから。 参照設定でADOが使えるようにしておくこと。 DBは作業しているブックとなる。

    Dim conn As ADODB.connection
    Set conn = New ADODB.connection
    With conn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0"
        .Open ThisWorkbook.FullName
    End With

次は SELECT 文を実行してみる。Sheet1のテーブルを全部読み込む。

    Dim rs As ADODB.Recordset
    Dim sql As String

    'SQLを作成
    sql = "SELECT * FROM [Sheet1$];" 

    'データを取得
    Set rs = New ADODB.Recordset
    rs.Open sql, conn, adOpenKeyset, adLockReadOnly

    '取得データをSheet2に展開
    '指定したセルを起点にまるっと貼り付ける
    Worksheets("Sheet2").Cells(1, 1).CopyFromRecordset rs

    '閉じるのを忘れずに
    rs.Close
    Set rs = Nothing

    conn.Close
    Set conn = Nothing
 

SQLは普通にJOINとかGROUP BYとかおなじみな感じで安心。 ただテーブル名やフィールド名の指定がエクセル独自な感じなのかなー。 この辺の書き方は要調査ですね。

ADODB.commandオブジェクトも使用可能みたいなので、パラメータも使えそう。 （今日はまだ試せてない。） 接続から切断までクラスモジュールにしておけばめんどくさくなくてよさげ。