★ExcelVBA ～ パワークエリ（PowerQuery）の更新方法いろいろ
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
パワークエリを更新する場合、
ListObjectオブジェクト（※QueryTable.BackgroundQueryがらみ）を使う方法や
WorkbookConnectionオブジェクト（※OLEDBConnection.BackgroundQueryがらみ）を使う方法などの、
複数の更新方法があるようですが、
どちらの方法でも、（「件数が多い場合」とかじゃなくて）、
「×××.BackgroundQuery = False」を設定しさえすれば、
ループでのDoEventsやWAITなどは不要で、普通に完了まで待ってくれるみたいです。
逆に、
「×××.BackgroundQuery = False」を設定しないと、
ループでのDoEventsあるいはWAITなどが必要になるようなのですが、
フリーズしてしまって進みませんでした。
いちおう、こちらでは以下の「★(01) 」「★(02) 」のいずれかのコードで更新できました。
複数のパワークエリの更新も、1つ目、2つ目、３つ目・・・・など、
それぞれに、「×××.BackgroundQuery = False」を設定しておけば、
それぞれ、自動的に待ってくれて、正常更新できました。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
★(01)
ListObject.QueryTable プロパティを使って、
パワークエリを更新する場合
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
' ' Sub PowerQueryRefresh_01() Dim o_Listobj As ListObject Set o_Listobj = Worksheets("Sheet1").ListObjects("table01") o_Listobj.QueryTable.BackgroundQuery = False '↑この設定忘れるとフリーズこくかも。 o_Listobj.Refresh MsgBox "更新が完了しました。" End Sub ' ' |
＊＊＊＊＊＊＊＊＊＊
なお、
Set o_Listobj = Worksheets("Sheet1").ListObjects("table01")
の
「table01」
は、パワークエリのテーブル名です。
リボンの「テーブルデザイン」タブで見ることができます。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
★(02)
WorkbookConnection.OLEDBConnection プロパティを使って、
パワークエリを更新する場合
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
' ' Sub PowerQueryRefresh_02() Dim o_WorkbookCn As WorkbookConnection Set o_WorkbookCn = ActiveWorkbook.Connections("Q1") o_WorkbookCn.OLEDBConnection.BackgroundQuery = False '↑この設定忘れるとフリーズこくかも。 o_WorkbookCn.OLEDBConnection.Refresh MsgBox "更新完了しました。" End Sub ' ' |
＊＊＊＊＊＊＊＊＊＊
ActiveWorkbook.Connections("Q1").OLEDBConnection.BackgroundQuery = False
の
「"Q1"」は
イミディエイトで
？ ActiveWorkbook.Connections(1).Name
とか
？ ActiveWorkbook.Connections(2).Name
で出てくる名前を使えばいいです。
自分は逆に、イミディエイトで、
ActiveWorkbook.Connections(1).Name = "Q1"
みたいに、自分の好きな名前に強制設定してしまってから、
その名前を使いました。
ひとまず以上ですが、ほかにも方法があるかもしれません。
【オマケ】
ブック内のすべての接続をリフレッシュ。リフレッシュが終わるまで待ちながら。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
' ' Sub PowerQueryRefresh_02() Dim o_WorkbookCn As WorkbookConnection ' Set o_WorkbookCn = ActiveWorkbook.Connections("クエリ - Sheet2org") ' o_WorkbookCn.OLEDBConnection.BackgroundQuery = False ' '↑この設定忘れるとフリーズこくかも。 ' o_WorkbookCn.OLEDBConnection.Refresh ' ' ActiveSheet.Range("A9") = "更新完しました" ' 'MsgBox "更新完了しました。" For Each o_WorkbookCn In ActiveWorkbook.Connections o_WorkbookCn.OLEDBConnection.BackgroundQuery = False o_WorkbookCn.OLEDBConnection.Refresh ActiveSheet.Range("A9") = "更新完しました" Next ' End Sub ' ' |
その際、その接続がどのシートのどのテーブル（パワークエリの出力先）にあるかも同時に調べながら、全部更新するコード（ChatGPTに聞いた）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
' ' Sub PowerQueryRefreshAndWsNmGetAndLObjNmGet01() Dim o_WorkbookCn As WorkbookConnection Dim ws As Worksheet Dim lo As ListObject ActiveSheet.Range("A9") = "" For Each o_WorkbookCn In ActiveWorkbook.Connections o_WorkbookCn.OLEDBConnection.BackgroundQuery = False o_WorkbookCn.OLEDBConnection.Refresh ' 各ワークシートを走査して、該当のListObjectを探す For Each ws In ActiveWorkbook.Worksheets For Each lo In ws.ListObjects If lo.SourceType = xlSrcQuery Then If lo.QueryTable.WorkbookConnection.Name = o_WorkbookCn.Name Then Debug.Print "接続名:「" & o_WorkbookCn.Name & " 」という接続は、ワークシート: 「" & ws.Name & " 」の『 " & lo.Name & " 』テーブルの中にあります。" End If End If Next lo Next ws Next o_WorkbookCn ActiveSheet.Range("A9") = "更新完了しました" End Sub ※イミディエイトウィンドウに以下のような感じで出ます。 接続名:「クエリ - Sheet2org 」という接続は、ワークシート: 「Sheet2org 」の『 Sheet2org 』テーブルの中にあります。 接続名:「クエリ - Sheet2org (2) 」という接続は、ワークシート: 「Sheet2org (2) 」の『 Sheet2org3 』テーブルの中にあります。 ' ' |
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
【オマケ２：パワークエリの場合の更新待ち】
●パワークエリの場合
（QueryTableオブジェクトを内包しているパワークエリの場合のみ）
まずは以下の内容のクラスモジュールを「clsQueryTableEvents」という名前で作成します。
コード丸ごとコピペでOK。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
' ' Option Explicit ' Class Module: clsQueryTableEvents Public WithEvents qt As QueryTable Public s_flg01 As String Private Sub Class_Initialize() s_flg01 = "" End Sub Private Sub qt_AfterRefresh(ByVal Success As Boolean) s_flg01 = "更新が完了しました。成功: " & Success End Sub ' ' |
で、標準モジュールからの呼び出し例。
これも丸ごとコピペでOKです。
QueryTableオブジェクトをRefreshすると、QueryTableオブジェクトのAfterRefreshイベントが呼び出されてから、つまり、「更新を待ってから」、セルに情報が書き込まれます。
F8キー実行をするとわかりやすいと思います。
「SetupQueryTableEvent02()」のほうを実行します。
状況に合わせられるなら、「SetupQueryTableEvent01()」でもいいですけど。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
' ' Option Explicit Dim qtEvents As clsQueryTableEvents Sub SetupQueryTableEvent02() Dim o_ItemLstObj01 As ListObject For Each o_ItemLstObj01 In ActiveSheet.ListObjects Range("A9") = "" Set qtEvents = New clsQueryTableEvents Set qtEvents.qt = o_ItemLstObj01.QueryTable qtEvents.qt.Refresh If qtEvents.s_flg01 <> "" Then Range("A9") = qtEvents.s_flg01 Else End If Next End Sub Sub SetupQueryTableEvent01() Range("A9") = "" Set qtEvents = New clsQueryTableEvents Set qtEvents.qt = ActiveSheet.ListObjects(1).QueryTable qtEvents.qt.Refresh If qtEvents.s_flg01 <> "" Then Range("A9") = qtEvents.s_flg01 Else End If End Sub ' ' |
★(03)複数びクエリを全て更新
ActiveWorkbook.RefreshAll
だけで行けるかもしれません。
その際、もし、うまくいかなかったら、
すべてのパワークエリの結果の表の
「バックグラウンドで更新する」のチェックをはずすと
いいかも？しれません。
データタブ
→「クエリと接続」の「すべて更新」ボタンの「▼」ボタンを押す
→「接続のプロパティ」
→「バックグラウンドで更新する」のチェックを外します。
これとは冒頭に書いた、
「×××.BackgroundQuery = False」
と同じ意味です。
Trueにするとバックグラウンド (非同期＝複数のクエリの場合は同時並行処理) で実行します。
Falseは複数のクエリの場合でも、１つ１つ、順番にやります。
以下、2007のヘルプより。
QueryTable.BackgroundQuery プロパティ
すべて表示
すべて非表示
True の場合、クエリ テーブルのクエリをバックグラウンド (非同期) で実行します。値の取得および設定が可能です。ブール型 (Boolean) の値を使用します。
OLEDBConnection.BackgroundQuery プロパティ
True の場合、 OLE DB 接続のクエリをバックグラウンド (非同期) で実行します。値の取得および設定が可能です。ブール型 (Boolean) の値を使用します。
QueryTable .Refreshing プロパティ
指定されたクエリ テーブルに対するバックグラウンド クエリが実行中である場合、このプロパティの値は True です。値の取得および設定が可能です。ブール型 (Boolean) の値を使用します。
OLEDBConnection.Refreshing プロパティ
指定された OLE DB 接続に対するバックグラウンド OLE DB クエリが実行中である場合、このプロパティの値は True です。値の取得および設定が可能です。ブール型 (Boolean) の値を使用します。
参考URL
「Code VBA」
https://www.codevba.com/Excel/WorkbookConnection.htm#.ZA1eK3bP1nJ
