Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
パワークエリの更新ボタンを作成したい
投稿日時: 21/02/06 12:46:18
投稿者: mugiro7

パワークエリの更新をするボタンを作成しましたが、更新途中でメッセージが表示されてしまう。
更新が終わった後にメッセージが表示されるようにしたい。
 
Sub code1() '2シート目から最終シートまでのクエリを実行
  
For i = 2 To Worksheets.Count
Worksheets(i).Select
Cells(2, 3).Select
 
Selection.ListObject.QueryTable.refreshbackgroundquery = False
Next
Worksheets(1).Select
ThisWorkbook.Save
Application.ScreenUpdating = True
MsgBox "更新終了"
 
End Sub

回答
投稿日時: 21/02/06 14:34:00
投稿者: simple

VBEのコードをそのままコピーペイストしてもらえますか?
手打ちによるミスが雑音として混入してしまうので。
 
BackgroundQueryオプションの説明(ヘルプ)は以下のとおりです。

引用:
QueryTables が SQL クエリの結果を基にしている場合にのみ使用します。データベース接続が確立し、クエリが実行されると直ちに制御をプロシージャに返す場合は True を指定します。QueryTable はバックグランドで更新されます。すべてのデータをシートに取り出した後で制御をプロシージャに返す場合は False を指定します。この引数を省略すると、BackgroundQuery プロパティの設定に基づきクエリ モードが決定されます。

これらの条件に合っていますか?

投稿日時: 21/02/06 15:32:40
投稿者: mugiro7

早速の返信ありがとうございます。
 
すみません。誤りがありました。(スペースと:が抜けていました)
誤)Selection.ListObject.QueryTable.refreshbackgroundquery = False
正)Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
 
Sub code1() '2シート目から最終シートまでのクエリを実行
For i = 2 To Worksheets.Count
Worksheets(i).Select
Cells(2, 3).Select
 
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Next
Worksheets(1).Select
ThisWorkbook.Save
Application.ScreenUpdating = True
MsgBox "更新終了"
End Sub
 
 
以下でも更新はできるのですが、同様に更新中にメッセージが表示されてしまいます。
 
Sub code2()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
MsgBox "更新終了"
End Sub
 
なので、Nextの前後で制御して待機などできないものかとおもったのですが、方法がわかりません。
PowerQueryでNWBIからデータを取得しているからか、手動更新でも同様に数分時間がかかります。
 
どうぞ、よろしくお願いします。

回答
投稿日時: 21/02/06 15:42:01
投稿者: simple

>QueryTables が SQL クエリの結果を基にしている場合にのみ使用します。
この部分はどうですか、外部DBなどをSQLで操作するケースに該当していますか?
PowerQueryはそれに該当しないとすると、非同期処理になるので、待つしか無いかも知れません。

投稿日時: 21/02/06 16:55:40
投稿者: mugiro7

ありがとうございます。
NWBIからDAX文でテーブルを取得していますが、使用できるコードに該当しないのですね。
手動で「すべて更新」でもメッセージは出ないので、いらないような気もするのですが、
できたら表示したいのです。

投稿日時: 21/02/10 18:35:49
投稿者: mugiro7

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
こちらのコードは、マクロの記録で「クエリを選択し、最新の情報に更新」で得たコードです。
実行結果も、正常に更新されます。
 
更新が終了したタイミングで、メッセージを表示させたい。
また、今後、終了後に別の処理を追加することになるので、更新が終わるまで次に進まないようにしたいのです。
よろしくお願いいたします。
 
 

回答
投稿日時: 21/02/15 14:03:14
投稿者: Suzu

バックグラウンドの更新設定はどうなっていますか?
 
 
とある会計士のひとりごと。
【【パワークエリ×マクロ】作成したクエリをボタンひとつで順番に更新する方法】
https://sakatakablog.com/excel/1775/

投稿日時: 21/02/17 20:20:26
投稿者: mugiro7

Suzuさま
返信、ありがとうございます。
 
バックグラウンドの更新設定のチェックは外していました。
 
For〜NextのシートSelect部分を、下記コードに変更し、複数シートを指定したところ、
まずまずのタイミングでMsgboxが表示されました。
 
Sheets("シート名").Select
Range("クエリ名[#Headers]").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
 
リンク先、大変勉強になりました。
ありがとうございました。