エクセル関数だととても複雑になってしまう処理を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オブジェクトも使用可能みたいなので、パラメータも使えそう。
(今日はまだ試せてない。)
接続から切断までクラスモジュールにしておけばめんどくさくなくてよさげ。