PowerQuery(パワークエリ)で作成したクエリをVBAマクロで実行する方法ついて紹介します。複数のクエリを順番に実行する方法も解説しています。
VBAマクロでクエリを更新する
マクロでクエリを更新する方法は下記の2つあります。ファイルに含まれる全てのクエリを実行する方法とクエリ名を指定して個別のクエリを実行する方法です。
- すべてのクエリを更新(RefreshAll)
- 一部のクエリを更新(Refresh)
全てのクエリを実行する
開いているファイルの全てのクエリを実行する場合は、下記のマクロを実行します。
Sub test()
ActiveWorkbook.RefreshAll
End Sub
【使用例】
下図のクエリAとクエリBをマクロで実行します。クエリAとクエリBは商品IDを用いて元データをフィルタするクエリであり、上述したマクロ 「test」を準備しておきます。
![](https://dono-log.com/wp-content/uploads/2023/10/画像305-1024x314.png)
元データを変更します。ここでは、6行目のAをBに変更しています。
![](https://dono-log.com/wp-content/uploads/2023/10/画像306.png)
- 開発タブにあるマクロをクリックする。
- クエリを実行するマクロを選択して実行をクリックする。
![](https://dono-log.com/wp-content/uploads/2023/10/画像307.png)
以上で全てのクエリの更新が完了です。
![](https://dono-log.com/wp-content/uploads/2023/10/画像308-1024x312.png)
個別のクエリを実行する
個別のクエリをクエリ名で実行する場合は、下記のマクロを実行します。
Sub test()
ActiveSheet.ListObjects("クエリ名").QueryTable.Refresh
End Sub
【使用例】
下図のクエリAをマクロで実行します。クエリAは商品IDを用いて元データをフィルタするクエリであり、上述したマクロ 「test」を準備しておきます。testのスクリプトは下記の通りです。
Sub test()
ActiveSheet.ListObjects("クエリA").QueryTable.Refresh
End Sub
![画像に alt 属性が指定されていません。ファイル名: 305-1024x314.png](https://dono-log.com/wp-content/uploads/2023/10/image-4.png)
元データを変更します。ここでは、6行目のAをBに変更しています。
![](https://dono-log.com/wp-content/uploads/2023/10/画像306.png)
- 開発タブにあるマクロをクリックする。
- クエリを実行するマクロを選択して実行をクリックする。
![](https://dono-log.com/wp-content/uploads/2023/10/画像307.png)
以上でクエリAの更新が完了です。クエリAのみが更新されており、クエリBは元のまま変わっていません。
![](https://dono-log.com/wp-content/uploads/2023/10/画像309.png)
Sponsored link
複数のクエリを順番に実行する(更新完了を確認)
複数のクエリを一つ一つ順番に更新完了させるには、下記のマクロを使用します。
下記マクロのquerylistには更新したいクエリ名をカンマ区切りで実行する順番通りに入力してください。 Do While~Loopの部分でクエリが更新完了するまで待つ処理をしています。
Sub test()
Dim querylist As Variant
Dim query As QueryTable
Dim queryname As Variant
querylist = Array("更新するクエリ名1", "更新するクエリ名2", ...)
For Each queryname In querylist
Set query = ActiveSheet.ListObjects(queryname).QueryTable
query.Refresh BackgroundQuery:=False
Do While query.Refreshing
DoEvents
Loop
Next
End Sub
※queylist,query,querynameなどの変数名は任意の名前に変更してください。
【使用例】
下図のクエリAとクエリBを順番にマクロで実行します。上述したマクロのquerylistに格納する値を「”クエリA”, “クエリB” 」としてマクロ「test」を準備しておきます。
Sub test()
Dim querylist As Variant
Dim query As QueryTable
Dim queryname As Variant
querylist = Array("クエリA", "クエリB")
For Each queryname In querylist
Set query = ActiveSheet.ListObjects(queryname).QueryTable
query.Refresh BackgroundQuery:=False
Do While query.Refreshing
DoEvents
Loop
MsgBox ("クエリ更新完了")
Next
End Sub
![](https://dono-log.com/wp-content/uploads/2023/10/画像305-1024x314.png)
元データを変更します。ここでは、6行目のAをBに変更しています。
![](https://dono-log.com/wp-content/uploads/2023/10/画像306.png)
- 開発タブにあるマクロをクリックする。
- クエリを実行するマクロを選択して実行をクリックする。
![](https://dono-log.com/wp-content/uploads/2023/10/画像307.png)
マクロ実行後、まずはクエリAが実行され完了後にメッセージボックスが表示されます。クエリAの結果だけが更新されています。
![](https://dono-log.com/wp-content/uploads/2023/10/画像310-1024x340.png)
メッセージボックスのOKをクリックすると、続いてクエリBが更新されて再度メッセージボックスが表示されます。このように順番にクエリが実行されます。
![](https://dono-log.com/wp-content/uploads/2023/10/画像311-1024x314.png)
Sponsored link
まとめ
今回は作成したクエリをVBAマクロで実行する方法ついて紹介しました。状況に合わせて下記のマクロを使ってみてください。
- 全てのクエリを実行
- クエリ名を指定して個別のクエリを実行
- クエリ名を指定して複数のクエリを実行
- 複数のクエリを順番に実行(クエリの更新が完了次第、順番に実行)
パワークエリの学習本についてはコチラの記事でまとめてますので参考にしてみてください。
・初心者向けおすすめ学習本
・初中級者向けおすすめ学習本
コメント
コメント一覧 (2件)
これって接続が一瞬で終わる前提で書いてますよね?
バックグラウンドクエリ実行が終わったかどうかを派手に知らせるポップアップを出したいんですけど
バックグラウンドクエリ完了のトリガーってなんなんです?
クエリ完了のトリガーはquery.Refreshing(クエリが実行中)ではなくなることとしています。
手順解説の中に一つのクエリが完了したらメッセージボックスを表示する例も記載していますので参考にしていただければと思います。