こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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


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

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

投稿日時 - 2018-12-03 00:40:11

QNo.9564071

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

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 <== 私の環境、課題ピボットテーブルの接続名です。

投稿日時 - 2018-12-04 09:40:08

ANo.1

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

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


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

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



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

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

投稿日時 - 2018-12-03 12:55:55

お礼

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

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

投稿日時 - 2018-12-03 15:37:54

あなたにオススメの質問