ExcelVBA 外部データのピボットを更新したい

質問

VBA初心者です。

アクティブなワークブックの非アクティブなシート3つにそれぞれひとつずつ計3つのピボットテーブルを作成しております。
ピボットテーブルはいずれも同じデータソースを利用しており、データソースは別のワークブックにあるテーブルです。

データソースが頻繁にデータが追加されるため、アクティブシートに設置したコマンドボタンからピボットテーブルのソースをまとめて更新したいと思うのですが、ワークブックとデータソースを収納するフォルダごと他者（Excelど素人）に渡すことがあるため、絶対パスを用いず、かつ「データソースに接続できません」というエラーを出さずにピボットテーブルを更新したいのですが…

ActiveWorkbook.RefreshAll
では、フォルダごとコピーしてコピー先のデータソースを更新しても別フォルダのはずのコピー元をいつまでも参照しており失敗

Sub Commandbutton1_Click()

Dim DATA_SOURCE As Worksheet
Dim DATA As String
Dim wb As Workbook
Dim PvtCache As PivotCache

DATA = ThisWorkbook.Path & “¥データ.xlsx”
wb = Workbooks(DATA)
Set DATA_SOURCE = wb.Worksheets(“データ”)

Set PvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=DATA_SOURCE)

End Sub

これでどうだと自分なりに記述したものの全く作動せず。
頻繁にブックがフォルダごとコピーや移動されるので都度同フォルダ内のソースを取得して更新したいのですが、可能でしょうか？

教えていただければ幸いです。

回答No.2

HohoPapa

Const MyText = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\MyTest\zzz\aaa\List2.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"

'Debug.Print MyText
'Debug.Print ActiveWorkbook.Connections("List2").OLEDBConnection.Connection

ActiveWorkbook.Connections("List2").OLEDBConnection.Connection = MyText

上記のようなコードで、課題文字列を修正できるようです。

※
List2　<==　私の環境、課題ピボットテーブルの接続名です。

回答No.1

HohoPapa

>ActiveWorkbook.RefreshAll
>では、
>フォルダごとコピーしてコピー先のデータソースを更新しても
>別フォルダのはずのコピー元をいつまでも参照しており失敗
このコードだから
>別フォルダのはずのコピー元
を参照してしまうのではなく、

ピボットテーブルごとに記憶している
接続のプロパティ、定義タブ、接続文字列（※）が
>別フォルダのはずのコピー元
を指しているものと思います。

※
ピボットテーブル上の任意のセルを選択し
リボンの分析、データソースの変更と辿ると確認できます。

この接続文字列をVBAから動的に書き換えることができるのか？
というのはわかりません。m(_ _)m

この設定を「外部データソースを使用」から
「テーブルまたは範囲を選択」に変更すると
相対的な設定となり、
フォルダーごと複写や移動をしても耐えられるかもしれません。（曖昧）

私だったら、
ピボットテーブルを使わず
自前でSQL文を投げ必要な集計を行うようにします。

お礼

回答ありがとうございます。
まさに＞接続文字列をVBAで動的に書き換える ことができればいいなあと思っております。
「Excel何それ？美味しいの？」という人にも渡る予定なのでピボットのあるシートは隠して接続ウィザードに触らせないようにしたいのです…

何分初心者のためVBAでできるかできないかの判断すらつかず…無理難題申し訳ございません