ExcelのPower Query更新を自動化する

ExcelのPower Queryでデータの取得・加工を行ったあとに、取得元のデータが変わっても自動的に更新されるわけではありません。

更新、すべて更新ボタンを押せばデータ更新可能です。「更新」はAlt＋[F5]、「すべて更新」はCtrl + Alt + [F5]のショートカットキーが割り当てられているため、多少は作業短縮できます。

ですが、、、

データ更新が頻繁にあり、日々データをチェックしてる場合は、手作業での更新は煩雑です。

そんなときはExcel標準機能を使うか、Power Automate、PowerShellを使いましょう。

自動化する方法は3点あります。

No.	自動化方法	使用する場面
1	Excelファイルを「開いた」ときに自動更新する	Excelで作業中に、クエリの更新忘れを防ぐ場合に使える
2	Excelファイルを「開いているときに一定時間経過」で自動更新する	Excelで作業中に、リアルタイムでデータが更新される場合に使える
3	Power Automate＋タスクスケジューラーを使って自動更新する	処理リサイクルが決まっている（毎月第1営業日や毎日朝12：00に実行するなど）場合に使える
4	PowerShell＋タスクスケジューラーを使って自動更新する	処理リサイクルが決まっている（毎月第1営業日や毎日朝12：00に実行するなど）場合に使えるプログラミングスキルが必要だが、その分やれることは多い印象

クエリの更新方法は5種類ある

自動化を考える前に、クエリの更新方法は5つあることを抑えておきましょう。

「クエリ」タブの「更新」ボタンを押せばデータが更新されます。

もしくは、「データ」タブの「すべて更新」ボタンを押せばデータが更新されます。

「更新」はAlt＋[F5]、「すべて更新」はCtrl + Alt + [F5]のショートカットキーが割り当てられているため、多少は作業短縮できます。

Excelファイルを開いたときにクエリを更新できます。
開いたときに必ず更新してくれるので、更新を忘れることは無くなりますが、クエリが重たい場合はファイルを開くのに時間がかかるため注意が必要です。

Excelファイルを開いている間に一定時間経過でクエリを更新できます。

「クエリプロパティ」で「定期的に更新する(R)」にチェックを入れると、更新間隔が分単位で設定可能です。
短い時間で更新すると負荷が大きくなるため注意が必要です。特にSQL ServerやOracleなどのデータベースを参照してる場合は、データベース側に負荷をかけてしまいます。

最短が1分ですが、不必要に短い間隔にしないほうが良いでしょう。

Excelマクロを使ってクエリを更新できます。RefreshAllかRefresh関数を使います。

すべてのクエリを更新する場合は次のようなVBAを記述します。

Sub QueryRefreshAll()

    ActiveWorkbook.RefreshAll

End Sub

一部のクエリを更新する場合は次のようなVBAを記述します。「販売データ」テーブルのクエリのみ更新されます。

Sub QueryRefresh()
        
    ActiveSheet.ListObjects("販売データ").QueryTable.Refresh

End Sub

ここまでは、Excelを開いてる場合しか更新が自動化できません。

「Excelを開く」というのは手間なので、頻繁に更新が必要な場合は大変です。

PowerShellを使えばExcelを開かず更新できます。次の記事で詳しく解説しています。

5つあるクエリ更新方法から、自動化方法について表にまとめました。

No.	クエリ更新方法	自動化可否	自動化方法
1	「更新」ボタンをクリックする	×	自動化対象外 ※Power Automateで実現できなくないが、Excelマクロを使うほうが簡単
2	クエリプロパティの「ファイルを開くときにデータを更新する」チェックを入れる	○	Excelファイルを「開いた」ときに自動更新する
3	クエリプロパティの「定期的に更新する」にチェックを入れる	○	Excelファイルを「開いているときに一定時間経過」で自動更新する
4	Excelマクロを使う	○	Power Automate＋タスクスケジューラーを使って自動化する
5	PowerShellを使う	○	PowerShell＋タスクスケジューラーを使って自動化する