VBAで「リンクされているExcelのワークシートを表示するための接続が切断されました」となり処理が中断します（エラーコードは1004）

Question

Excel2013（OSはwin8.1)で、VBAにて、以下のようなマクロを実行しようとしていますが、

「リンクされているExcelのワークシートを表示するための接続が切断されました」となり処理が中断します（エラーコードは1004）

■やりたいこと

　あるピボットで作られたレポートを、外部データソースのみ切り替えて更新したい。

■経緯

　以前は、「テーブルの範囲を選択」でパスを切り替えていたのですが、この方式だと、ピボット更新の都度、Officeキャッシュが

生成されるので、i/oが頻発し処理時間が遅いのと、このキャッシュが残ってしまいCドライブの枯渇を招いていました。（手動で削除しないといけません）。マイクロソフトに問い合わせた（他人経由で）ところ、ピボットのデータソースを「外部接続」にすれば当該事象は解消されるとのことでした。実際に手動で実験をしてみたところ、この事象は回避できるようです。

　そこで、これをVBAマクロで実装しようと、いろいろネットで調べながら試行しているのですが、手当たり次第ネットで検索してでてきた言葉（「OLEDB」など）すら正しく理解できておらず、コードも見様見真似です。

要件に対して、そもそも実現方式が間違っているのでしょうか？それとも、他の要因でこのような事象が発生するのでしょうか？

■処理概要

　外部データ接続で、共有フォルダの別パスにあるEXCELファイルをデータソースにしてピボットを作成します。

　一度はうまくできたのですが、

　要件として、他のデータソースに切り替えたいため、一旦ピボットや、接続名を手動で消しました。

　（同一範囲と重なる行列には、外部接続によるピボットが生成できないようでしたので）

　手動で削除後、再度、処理を実行すると、当該エラーとなってしまいます。

　目視では、既存の接続の”このブック内の接続”や”ネットワーク上の接続ファイル”からは「接続は見つかりません」となっています。

　”このコンピュータ上”には、これまで作られた（過去のが蓄積されていくのでしょうか？）ものが残っているようです。

　2回目以降は、シート一度消したり、PCをReBOOTしたりも試みましたが、解消しません。

コードは↓　で最後の.CreatePivotTableで中断してしまいます。

---

objOpeSheet.Activate

str_sht代表 = "ピボットシート"

str_pvt代表 = "pvt_TBL

str_srcSHname = objSrcSheet.Name

     Dim pName As String, srcPath As String

     Dim ptCache As PivotCache, ptObj As PivotTable

     Dim cnn As String, tblName As String, sql As String

　 srcPath = strPathname & objSrcBook.Name    ' ソースデータのパス

     cnn = "ODBC;DSN=Excel Files;DBQ=" & srcPath

     tblName = str_srcSHname & "$"

    sql = "SELECT * FROM [" & tblName & "];"

    objPvtBook.Worksheets(str_sht代表).Activate  

    Set ptCache = objPvtBook.PivotCaches.Create(SourceType:=xlExternal)

    ptCache.Connection = cnn

    ptCache.CommandText = sql

    Set ptObj = ptCache.CreatePivotTable( _

        TableDestination:="R1C1", TableName:=tblName)

---

Answer 1

松本　七生   様　ご教示ありがとうございました。VBAというより、外部接続に関する問題かと思いました。念のため、VBAフォーラムへも投稿してみます。

Answer 2

TaroSasaki さん、こんにちは。

マイクロソフト コミュニティのご利用、ありがとうございます。

Excel 2013 でマクロの実行時にエラーメッセージが表示され、処理が中断するのですね。

今回投稿していただいた内容の場合、基本的な使い方やトラブルの対処法について情報交換を行っているマイクロソフト コミュニティでは情報が集まりにくい可能性があります。

せっかくこちらに投稿していただいたのですが、マクロ実行時に起こる問題ということであれば専門の VBA フォーラムに投稿されるとアドバイスが集まりやすいと思います。

◆ Msdn フォーラム - Visual Basic for Application (VBA)

よろしければ、ご利用ください。