【ExcelVBA】クエリの更新とピボットの更新を自動化するには？

PowerQueryでCSVを取り込んだので、
同じファイル名のCSVを差し替えればシート内のデータが変わるようにしています。
そのデータシートからピボットで集計もしています。

ただ、以下の操作が毎回手作業なので
・データメニューから「すべて更新」
・ピボット上で右クリック→更新

これを自動化させたく、以下のソースで「更新」ボタンを作りました。(マクロの記録で作成)
----------
Sub クエリとマクロ更新()
ActiveWorkbook.RefreshAll
ActiveSheet.PivotTables("ピボットテーブル2").PivotCache.Refresh
End Sub
----------
しかし、1回クリックするとCSVは更新されるようですが、ピボットの更新がされず、
もう1回クリックするとピボットの中身が更新されます。
結局、上記で作成した「更新」ボタンを2回押さないと目的が達成されません。

何が原因でしょうか？
1回で、クエリーの更新及びピボットの更新を出来ないでしょうか？
また、何もボタンを押さずにマクロブックを開いたら自動更新は出来ないでしょうか？
(読み込み時間が多くなるなら手動で行います)

No.1 回答者： ママチャリ 回答日時： 2021/04/01 09:27

DoEvents 関数を挟んだら、うまくいきませんかね？

この回答へのお礼

以下の様に「DoEvents」を間に入れたのですが駄目でした。

ActiveWorkbook.RefreshAll
DoEvents
ActiveSheet.PivotTables("ピボットテーブル2").PivotCache.Refresh

やはり2回目に押すた時にピボットが更新されます。
代わりにMsgBox表示を挟ん出も駄目でした。
OKを押すまでステータスバーに「バックグラウンド クエリを実行しています」が出たままで進んでないようなので、中断しちゃってるようです。
OKを押すと完了するようで、やはり2回目の実行でピボットが更新されました。

バックグラウンド クエリーが完了するまで待機できると良さそうですが、
まだ方法が分かりません。

お礼日時：2021/04/01 10:10

No.2 回答者： Qchan1962 回答日時： 2021/04/01 15:32

こんにちは

接続プロパティなど不明な点もありますが、自身の探求の為、事象を探すと
下記のような記事を見つけました。
バックグラウンドリフレッシュの設定に原因があるようですが、
上記サイト内のサンプルコードで、実験したところ期待する結果を得られそうです。
詳細に付いては、参考サイトをご覧ください。

抜粋コードです。

Dim objConnection As Object
Dim bBackground As Boolean
For Each objConnection In ThisWorkbook.Connections
bBackground = objConnection.OLEDBConnection.BackgroundQuery
objConnection.OLEDBConnection.BackgroundQuery = False
objConnection.Refresh
objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next
ActiveSheet.PivotTables("ピボットテーブル2").PivotCache.Refresh

実行内容
すべての接続をループ
BackgroundQueryを取得し保管
BackgroundQueryを Falseに設定
Refreshを実行
BackgroundQueryを保管値で元に戻す
ループ
PivotCache.Refresh

終了

この回答へのお礼

ありがとうございます！
お礼が遅くなりましたがやりたい通りに動作しました。

お礼日時：2021/04/08 18:27

No.3 回答者： Qchan1962 回答日時： 2021/04/01 15:34

＃２　表記忘れ

参考サイト
https://stackoverflow.com/questions/22083668/wai …

No.4 回答者： Qchan1962 回答日時： 2021/04/01 17:25

>また、何もボタンを押さずにマクロブックを開いたら自動更新は出来ないでしょうか？

これは、Workbook_Openなどに記述（からCall）すれば良いように思いますが、試していません。
一応、該当クエリ、ピボットのみを更新する場合は、下記のような感じでOKかと
Sub Refresh_Query_Pivot()
Dim tmp As Boolean
With ActiveWorkbook.Connections("クエリ - xxx")
tmp = .OLEDBConnection.BackgroundQuery
.OLEDBConnection.BackgroundQuery = False
.Refresh
.OLEDBConnection.BackgroundQuery = tmp
End With
Worksheets(1).PivotTables("yyy").PivotCache.Refresh
End Sub

Worksheets(1)　Workbook_Open時にActiveSheetになっている保証がないのでシートをインデックスで指定していますが、(1)は仮定、不具合がある場合は、対象シートを.ActivateでActiveSheetにしてCallするのが良いと思います。

xxx , yyyは、対象の名前です。

この回答へのお礼

無難に、目視で確認してからボタンを押す仕様にしました。
ありがとうございます。

お礼日時：2021/04/08 18:28