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

パワークエリの更新→反映を待つ→ユーザーフォームを表示をする方法
　　
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
　　
　　
調べてみましたところ、以降の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
'
'

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

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

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

以下、前述の２つの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
'
'

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です。

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

MENU

関連記事

最近の投稿

カテゴリー

MENU

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

関連記事

最近の投稿

カテゴリー

タグ

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