★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"
みたいに、自分の好きな名前に強制設定してしまってから、
その名前を使いました。
ひとまず以上ですが、ほかにも方法があるかもしれません。
★(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
