パワークエリの更新→反映を待つ→ユーザーフォームを表示 をする方法

調べてみましたところ、以降のtest01()やtest02()のようなコードでできるっぽいです。
(※自分はVBA初心者なのでダメだったらごめんなさい。ただ、一応、当方では簡単なつくりのパワークエリはこれで更新+反映+ユーザーフォーム開き、ができました。)

Sub test01()

Dim s_TblName As String
Dim o_ListObj As ListObject
s_TblName = "パワークエリの表のテーブル名"
Set o_ListObj = ActiveSheet.ListObjects(s_TblName)

o_ListObj.QueryTable.BackgroundQuery = False
o_ListObj.Refresh '更新

VBAProject.UserForm1.Show
'↑ユーザーフォームを開く

o_ListObj.QueryTable.BackgroundQuery = True

End Sub

Sub test02()

Dim s_TblName As String
Dim o_ListObj As ListObject
s_TblName = "パワークエリの表のテーブル名"
Set o_ListObj = ActiveSheet.ListObjects(s_TblName)

Dim s_QryName As String
Dim o_Con As WorkbookConnection
s_QryName = "パワークエリの表のクエリ名"

o_ListObj.QueryTable.BackgroundQuery = False
Set o_Con = ActiveWorkbook.Connections("クエリ - " & s_QryName)
o_Con.Refresh

VBAProject.UserForm1.Show
'↑ユーザーフォームを開く

o_ListObj.QueryTable.BackgroundQuery = True

End Sub

test01のほうが簡単かも?です。

なお、「待つ」のに、以下のようなDoEventを使うコードを試したら、
無限ループに陥って、抜け出せなくなってしまいました。

Do Until o_ListObj.QueryTable.Refreshing
DoEvents
Loop

↑MicrosoftQuery(=QueryTableオブジェクト単体)の更新+反映の場合は、これでOKだった気がするのですが・・・。

************************
************************
ちなみにですが、
「ユーザーフォームが先に開いてから、数秒以内に更新が表に反映されるのでもいい」
ということなら、以下の(A)(B)の2行コードのいずれかで大丈夫っぽいです。

(A)
ActiveSheet.ListObjects("パワークエリの表のテーブル名").Refresh
VBAProject.UserForm1.Show vbModeless

「"パワークエリの表のテーブル名"」は、テーブル内のどこかのセルをクリックしたのち、リボンの「テーブルデザイン」の「テーブル名」に表示される名前です。

(B)
ActiveWorkbook.Connections("クエリ - ×××").Refresh
VBAProject.UserForm1.Show vbModeless

※↑「"クエリ - ×××"」の「×××」の部分は、パワークエリの表の「クエリ名」のほう。
見つけ方は、
パワークエリのテーブルを右クリック
→テーブル→外部データのプロパティ
→「クエリ」のところの名前。(その右のボタンの押下で出てくるダイアログでコピーできます)

************************
************************

以下、前述の2つのSubプロシージャをテストするときに使った調査段階のコードです。コメントをだらだらと書いてますが、何かのご参考になれば・・・

Sub sss()

Dim s_TblName As String
Dim o_ListObj As ListObject
s_TblName = "_Q01" '←パワークエリの表の「テーブル名」のほうを書く
Set o_ListObj = ActiveSheet.ListObjects(s_TblName)

'更新方法ーその1 ***********
o_ListObj.QueryTable.BackgroundQuery = False
' ↑なぜかこれやっとくとユーザーフォームが開く前
' に更新が反映されるっぽい。
o_ListObj.Refresh
'更新方法ーその1 ***********

' '更新方法ーその2 #############
' o_ListObj.QueryTable.BackgroundQuery = False
' ' ↑なぜかこれやっとくとユーザーフォームが開く前
' ' に更新が反映されるっぽい。
' Dim s_QryName As String
' Dim o_Con As WorkbookConnection
' s_QryName = "Q01" '←パワークエリの表の「クエリ名」のほうを書く。
' '見つけ方は、パワークエリのテーブルを右クリック
' ' →テーブル→外部データのプロパティ
' ' →「クエリ」のところの名前。
' Set o_Con = ActiveWorkbook.Connections("クエリ - " & s_QryName)
' o_Con.Refresh
' '更新方法ーその2 #############

DoEvents '←いちおう念のためにやっただけ。要らなければ消す。

''「××××.QueryTable.BackgroundQuery = False」を
''やらない場合は、
''ユーザーフォームをモードレスで開かないと
''なぜか更新が画面に反映されないようなので
''その場合なら、モードレスで開く
''↓
'VBAProject.UserForm1.Show vbModeless

''↑ただし、
''「××××.QueryTable.BackgroundQuery = False」を
''やらない場合は、
''先にフォームが開いてから、
''そのあと更新が終わる形になってしまうっぽい。
''詳細は未調査。
''ただ、一応、ユーザーフォームを開いてパワークエリを更新するのに
''最短で2行で終わる。(今回はやってません)
''もちろん、
''「××××.QueryTable.BackgroundQuery = False」を
''やって、かつ、モードレスで開いてもOK。

Debug.Print ActiveSheet.Range("C2").Value
'↑更新反映チェック用。テスト時以外はコメントアウト

VBAProject.UserForm1.Show
'↑ユーザーフォームを開く

o_ListObj.QueryTable.BackgroundQuery = True
''↑「××××.QueryTable.BackgroundQuery = False」を
''やらない場合は、コメントアウトする

End Sub

******************
******************

o_ListObj.QueryTable.BackgroundQuery = False
o_ListObj.QueryTable.BackgroundQuery = True
は、
https://ecoslyme.com/excel-powerquery-auto-renewal/
の、
「クエリの「バックグラウンドで更新する」のチェックを外す(オフにさせる)」
のようなことをVBAでON/OFFしてます。

手動の設定画面↓
https://ecoslyme.com/wp-content/uploads/2021/07/img_60f400553b83d.png

なので、ここでチェックをはずしておけば、
「××××.QueryTable.BackgroundQuery = ×××」のコードは不要といえば不要です。

例えば、
「o_ListObj.QueryTable.BackgroundQuery = True」は、
一応念のためにデフォルトの設定に戻してるだけなので、不要ならコメントアウトしてもOKです。

ただ、
その手動のチェック入れ/外し、の設定変更がトラブルを誘うといけないので、サンプルでは念のためにデフォ設定に戻しました。