Excel マクロ SQL実行について

質問

いつもお世話になっております。

現在、Excel1997と2007でマクロを作成しております。
使用している環境は様々でOSは2000～XPまでです。

っで質問なんですが、「マクロ」というシートと「データ」というシートがあったとして、「マクロ」シートにはボタンがあり、ボタンを押すとマクロが実行します。

マクロが実行されると「データ」というシートにあるデータをマクロ内のSQLで集計して、新しい「出力」というシートに吐き出します。

現在マクロ内のSQLはDAOライブラリを使用しています。EXCELマクロ内でSQLを使用する場合にDAOやADO等のライブラリを使用せずにSQLを実行することは可能でしょうか？

回答No.2

end-u

>EXCELマクロ内でSQLを使用する場合にDAOやADO等のライブラリを使用せずにSQLを実行することは可能でしょうか？
可能です。QueryTable オブジェクトを使う事になります。

Sub try()
　　Dim wkSHT As Worksheet
　　Dim wkQRY As QueryTable
　　Dim wkCON As String
　　Dim wkSQL As String

　　wkCON = "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";"
　　wkSQL = "SELECT [field1],[field2] FROM [データ$] " & _
　　　　　　"WHERE [field1]='1234' ORDER BY [field2]"
　　Set wkSHT = ActiveSheet
　　Set wkQRY = wkSHT.QueryTables.Add(Connection:=wkCON, _
　　　　　　　　　　　　　　　　　　　Destination:=wkSHT.Range("A1"), _
　　　　　　　　　　　　　　　　　　　Sql:=wkSQL)
　　With wkQRY
　　　　.RefreshStyle = xlOverwriteCells
　　　　'.AdjustColumnWidth = False '2000以降のプロパティで97にはない。
　　　　.Refresh BackgroundQuery:=False
　　　　'wkSHT.Names(.Name).Delete '名前定義削除
　　　　'.Delete 'QueryTable削除（上の行とセットで必要に応じ。）
　　End With

　　Set wkSHT = Nothing
　　Set wkQRY = Nothing
End Sub

97で[データ]-[外部データの取り込み]-[新しいクエリーの作成]をマクロ記録してみると理解し易いと思います。
2007は[データ]-[外部データの取り込み]-[その他のデータソース]-[MicrosoftQuery]です。

ですが、ADO、DAO、QueryTableを使って自Bookにアクセスする場合はメモリリークが激しいので連続実行は避けたほうが良いです。
http://support.microsoft.com/kb/319998/ja

それよりもExcelの基本機能のAdvancedFilterメソッド（[データ]-[フィルタ]-[フィルタオプションの設定]のこと）を
使った方が良いです。
http://www11.plala.or.jp/koma_Excel/contents6/mame6042/mame604201.html
（手作業では）抽出先を選択して実行すれば別シートに抽出できますし、
予め抽出フィールドの項目名をセットしておけば必要なフィールドだけ抽出する事もできます。

お礼

お礼がかなり遅れました。スイマセン

この回答は素晴らしい！まさに自分が求めてた回答でした。

しかも、ユーザーDSNもマクロで宣言できるように組めば、どんな環境でも実行できるマクロを組むことができますね♪

大変勉強になりました。ありがとうございます！！

Excelの機能で集計などできることは知ってはいるのですが、あまり詳しくない自分にとってはSQLの方が簡単にできてしまうので、SQLがどの環境でも使えるマクロが組めてホント助かりました＾＾

点数なんですが２０点付けて締め切ったはずが。。。。。点数が付いていないorz　大変申し訳ございません。。

回答No.1

rivoisu

やろうとしていることはシート内のデータを集計してシートに出力するのですからSQLを使わなくとも普通にできると思います。
SQLを使うならADOまたはDAOライブラリーは必要だと思います。

お礼

ＳＱＬでやりたいのです。。。。

回答ありがとうございました。