Power Queryの更新が完了するのを待つ方法は? 「BackgroundQuery = False」で「DoEvents」を試しましたが、何も機能しません

Power Queryの更新が完了するのを待つ方法は? 「BackgroundQuery = False」で「DoEvents」を試しましたが、何も機能しません

タグ:




質問

セットアップ:

  • Windows 7(作業中)
  • Windows 10(自宅)
  • Excel 2016(動作中のビルド4627)
  • Excel 2016(自宅で8730をビルド)
  • Power Queryは、Excelファイルのフォルダーをインポート、追加、および変換するように設定されています。このステップは機能します。

問題の説明

以下に示す「試したこと:」セクションで説明されているように、任意の手法を任意に組み合わせて使用​​し、PowerQueryが更新を完了するのを待った後。 [クエリと接続]ペインに表示される更新インジケーター(スピナー?)アイコンに従って、Power Queryテーブルの更新が完了する前に、メッセージボックスを表示し、その他のコードを実行できます。

上記のステートメントの例外は、以下の「コード」セクションに示されているApplicationクラスの「OnTime」メソッドです。これは、電源クエリの更新のポーリングを中断するようには見えません。ただし、この方法の問題は、ハードコードされた時間を使用してVBAコードを一時停止することです。これは、クエリされるデータのサイズ、量、および期間が時間とともに変化するため、常に機能するとは限りません。

私が試したこと:

  • 「DoEvents」、「BackgrgoundQuery = False」、CalculateUntilAsyncQueriesDoneのメソッドとプロパティの使用について説明しているすべてのStackOverflow(およびその他のWebサイトリソース)を読みました。
  • このリンクで提案されているように、クエリ更新イベントの前後に作成するクラスを作成しようとしました(以下のコード例には示されていません)。
  • QueryTableメソッドの.Refreshing = True / Falseプロパティを指定してDoUntil / Whileループを使用して、更新が終了するのを待ちました。
  • ここで「subro」によって提案されているように、Excelメニュー(メニューバー->データ->接続->プロパティ)のBackgroundQueryプロパティをFalseに設定しようとしました:ActiveWorkbook.RefreshAllが終了するまで待つ-VBA 、ここにメニューの画像があります:

コード:

Private Sub sht_sub_Refresh_AllConnections_dev()
‘Name: sht_sub_Refresh_AllConnections_dev
‘Purpose: An attempt at using VBA to wait for Queries to finish updating before displaying a message.
‘Description: Waits for a hard coded period of time before dislpaying the message box.
‘State: WIP.
‘Dev: Needs a way to look at the connection stream to somehow detect when its finished.

‘DECLARATIONS:
‘————‘
Dim procName As String ‘Stores this procedure’s name.
Dim qTblLst As QueryTables ‘A query table collection object.
Dim qTblObj As QueryTable ‘A query table object.
Dim conLst As Connections ‘A connection collection object.
Dim conObj As WorkbookConnection ‘A connection object.
Dim idx As Long ‘A loop counter.

‘INITIALIZATIONS:
‘—————‘
procName = “sht_sub_Refresh_AllConnections_dev” ‘Store this procedure’s name.
Linit.ini_Setup_Project ‘Setup the project if needed.
Set conLst = ThisWorkbook.Connections ‘Set the connections list object.
Set conObj = conLst.Item(conLst.Count) ‘Set an initial connection object.
idx = 0 ‘As an exit if the do loop continues without end.

‘MAIN CODE BODY:
‘————–‘
‘Turn off backgroundquery for each connection type.
For Each conObj In conLst ‘For each connection object,
With conObj
Select Case .Type ‘Check the connection type,
Case 1 ‘If its an OLEDB connection then,
.OLEDBConnection.BackgroundQuery = False ‘Set it’s backgroundquery property to false.
Case 2 ‘If its an ODBC connection the,
.ODBCConnection.BackgroundQuery = False ‘Set it’s backgroundquery property to false.
End Select
End With
Next conObj

ThisWorkbook.RefreshAll ‘Refresh all connections.

‘DEV: Using loops, DoEvents and a query name starting with the letters “zzzz” as suggsted here:
‘https://social.technet.microsoft.com/Forums/en-US/bc3f7748-8a52-498d-951c-4566b8adf45a/in-excel-2016-power-queries-dont-refresh-in-the-background-anymore?forum=powerquery
‘and here:
‘https://www.myonlinetraininghub.com/excel-forum/vba-macros/pause-macro-until-power-queries-finished-refreshing
‘Attempt to wait until the last connection has finished refreshing.
Do Until Linit.gvTbl_ZZZZZ.QueryTable.Refreshing = True ‘Wait until the last table starts refreshing,
idx = idx + 1 ‘Icrement a loop count,
If idx > 3000 Then Exit Do ‘If the loop goes longer then 3000 iterations exit,
Loop ‘otherwise continue waiting.
VBA.DoEvents ‘Do events before continueing (doens’t work).
Do Until Linit.gvTbl_ZZZZZ.QueryTable.Refreshing = False ‘Wait until the last table finishes refreshing,
idx = idx + 1 ‘Icrement a loop count,
If idx > 3000 Then Exit Do ‘If the loop goes longer then 3000 iterations exit,
Loop ‘otherwise continue waiting.
VBA.DoEvents ‘Do events before continueing (doens’t work).
‘DEV: The following is an attempt to get connections to
‘ finish refreshing before code continues as suggested here:
‘https://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba
Application.CalculateUntilAsyncQueriesDone ‘This is placed here as well as after the refresh.
VBA.DoEvents ‘Do events before continueing (doens’t work).
Application.EnableEvents = False ‘Maybe turning off events helps? (nope…),
Application.ScreenUpdating = False ‘This is reset in the procedure called as an argument to the next line:
Application.OnTime DateAdd(“s”, 3, Now), _
“Lwksh.sht_sub_Msg_RefreshDone” ‘The called procedure just displays a message box.
Application.EnableEvents = True ‘Restore events,
Application.ScreenUpdating = True ‘Restore screen updating.

‘MEMORY CLEANUP:
‘————–‘
EXIT_CLEAN:
procName = Empty
Set qTblLst = Nothing
Set qTblObj = Nothing
Set conLst = Nothing
Set conObj = Nothing
idx = 0
End Sub

コードノート:

  • 「Linit」が前に付いているコード内のすべて。コードの「INITIALIZATIONS:」セクションの「Linit.ini_Setup_Project」プロシージャコールによってプロシージャの外部でグローバルに設定されるオブジェクトまたは変数です。
  • たとえば、「Linit.gvTbl_ZZZZZ」は、「zzzz」という文字で始まる名前を持つ空の1行のテーブルを指すオブジェクト変数であり、Power Queryによって生成され、Excelシートに読み込まれます。このコードは、このような空のテーブルを使用するための提案が行われたWebサイトへのリンクを示しています。

質問

  1. これは、Power Queryに、更新プロセスの更新が完了したことをExcelに通知するためのコールバックが組み込まれていないために失われた原因ですか?
  2. これが失われた原因ではない場合、接続の更新がまだ完了していない場合に何らかの方法でエラーが発生するようにトリガーしたり、接続が終了したときにエラーをトリガーしたりするために使用できる、ここで説明されていない他の方法はありますか? ? (ここでの考え方は、このエラーが天気を検出するための可能な方法としてトラップされる可能性があるか、エラーがクエリの終了を停止しない限り、更新が完了していない可能性があるということです)。
  3. VBAを使用して接続ストリームを直接プローブし、接続が閉じているか終了した状態を探す方法はありますか?
  4. C#やPythonなどの他の言語で書かれたExcelの外部のプログラムを呼び出すことで、更新プロセスに直接アクセスする方法はありますか?
  5. これを機能させるために試行またはテストされる可能性のある他の何かを考えられますか?私は自分で答えを探し続けますが、1年間検索した後、私は少し運が悪いと感じています。

この問題にご関心をお寄せいただき、ありがとうございます。情報を含めるのを忘れた場合、またはこの質問をより明確にするために何かを言い換える必要がある場合は、コメントで知らせてください。

ベストアンサー

@neurojellyの痛みを理解しました。私はそこに行ったことがあります。しかし、結局のところ、解決策は非常に単純であり、VBAを使用していません。 [クエリのプロパティ]ウィンドウで、次のことを行う必要があります チェックを外します 「バックグラウンドリフレッシュを有効にする」をクリックしてから DoEvents を使用します。私はこの方法を1年以上使用しているので、これが機能することは確かです。

コードが含まれているサンプルファイルへのリンクを見つけてください。
https://drive.google.com/open?id=1ZLxSMEXPLda3QhaQoTyGGv3_sC-tpN-X

2番目の質問については、 Iferror/OnEror メソッドを使用して、クエリがエラーを返すかどうかを検出できますが、必ずしもクエリのエラーをチェックするわけではありません。クエリ自体がエラーポップアップを返しているかどうかを識別します。エラーポップアップは、VBAコードの実行中にデフォルトでスキップされます。この方法はほとんどの場合機能しますが、常に機能するとは限りません。







source
MarkLogicのスポーン機能が完了するのを待つ方法
ThreadPoolExecutorが完了するのを待つ方法
QueueUserWorkItemが完了するのを待つ
.forEach()が完了するのを待つ最良の方法
Canceledタスクが完了するのを待つ方法
ラムダが完了するのを待つ適切な方法
いくつかのスレッドが完了するのを待つ方法は?
Kubernetesでロールアウトが完了するのを待つ方法
Task.Runが完了するのを待つ方法
レトロフィット2:@Query “encoded = false”が機能しない
Rating