[VBA] PowerQueryとピボットテーブルを順序を指定して更新する方法

ここではクエリの名前を指定して更新した後、RefreshAllでピボットを一括更新しています。

ピボットの元データに複数のPowerQueryデータを使っている場合は、先に元の表だけ更新したいですよね。クエリが別のクエリを参照している場合もこの方法は有効です。

サンプルコード

元データの順序を指定して更新した後、複数存在するピボットテーブル全て一括更新する。

Sub RefreshSample()
  With ThisWorkbook
    .Connections("クエリ - テーブル1").Refresh　’PowerQueryの指定したテーブルだけを更新
    .Connections("クエリ - テーブル2").Refresh　
    .RefreshAll　　　　　　　　　　　　　　　　　　’ピボットテーブルだけを更新
    .Save
  End With
End sub

RefreshAllは便利ですが、更新の順序が想定外のことがありますので、明示的に順序をしておくといいですね。ピボットも順序指定したいことがありますね。この部分はまた、追記しようと思います。

急ぐ方はマクロの自動記録でピボットを更新してコードを確認してみると良いでしょう。

注意点

この方法を使う時に以下の設定をしておくと良いでしょう。

「全て更新」でクエリの更新を除外しておく

.RefreshAllの時点では既にクエリの更新が終わっているので、クエリの除外してピボットだけが更新されるようにしておきます。

おまけ：テーブルのおすすめ設定

テーブルを扱う際に私がよく使う設定をご紹介します。

クエリの列幅を自動で調整させない

PowerQueryでデータが更新されるたびに列幅が更新されるのが煩わしいので、自動で調整させないようにしています。欲しいのは値だけですので、この方が私はすっきりします。

ピボットテーブルの列幅を自動で調整させない

ピボットテーブルも列幅の自動調整をはずしています。

好みのテーブルデザインをデフォルトに設定しておく

PowerQueryで取得した表は自動的にテーブルとなります。その時に設定されるデザインをデフォルト登録することができます。設定しておくとテーブル作成時にそのデザインが適用されます。

そうでないと、あの緑色のシマシマ模様になります。私はすっきりとしたのが好きなので、タイトルだけに色がついたものをデフォルトにしています。ファイルごとの設定となりますが、簡単ですのでよく使う方は設定しておくと良いでしょう。

なごみ

おまけの部分お好みです。ご参考として紹介しました。
気になる方はぜひお試しを。

では、また。