エクセルVBAでエラー！

エクセルでVBAを組んでいます。
Aシート・Bシートにデータがあり、それをSQLで集計し、
Cシートに出力をしたいのですが、

『リンクされているExcelのワークシートを表示するための接続が切断されました。』

というエラーが出て、解決しません。
（調べてみましたが、似たような例がなく解決には至りませんでした・・・。）

どなたかご教示お願い致します。

Private Sub CommandButton1_Click()

Dim dbCon As Object
Dim dbCols As Object
Dim dbRes As Object
Dim strSQL As String

Dim sh1 As Worksheet

Set sh1 = Worksheets("Cシート")

Set dbCon = CreateObject("ADODB.Connection")
dbCon.Provider = "Microsoft.Jet.OLEDB.4.0"
dbCon.Properties("Extended Properties") = "Excel 8.0"
dbCon.Open ThisWorkbook.FullName

strSQL = ""
strSQL = strSQL & "SELECT *"
strSQL = strSQL & vbCrLf & "FROM [Aシート$] LEFT JOIN [Bシート$] ON [Aシート$].NO= [Bシート$].NO"

Set dbRes = CreateObject("ADODB.Recordset")

dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly

sh1.Range("A1").CopyFromRecordset dbRes

dbRes.Close
Set dbRes = Nothing
dbCon.Close
Set dbCon = Nothing

End Sub

No.1 回答者： mitarashi 回答日時： 2013/02/22 22:51

直接の回答にはなっておりませんが、自ブックをADOで処理＝必然的に開いているブックをADOで処理となりますが、メモリリークが発生してトラブる様ですので、方針を変更された方が良いと存じます。

リンク先では、自ブックのコピーを別名保存してそちらから抽出するという回避策にも言及されています。
http://support.microsoft.com/kb/319998/ja

なお、Microsoft.ACE.OLEDB.12.0プロバイダでは対策されている様です。

この回答へのお礼

回答ありがとうございます。
お礼が遅くなり申し訳ありません。

諸事情でなるべく自ブックのみでマクロを組みたいのです。
QueryTableオブジェクトが、ADO・DAOの代わりになるのを知ったので、
それを試してみて、ダメだったらコピー参照の方法を取りたいと思います。

お礼日時：2013/03/13 18:52