Setup:
- Windows 7 (at work)
- Windows 10 (at home)
- Excel 2016 (build 4627 at work)
- Excel 2016 (Building 8730)
- Power Query is configured to import, add, and convert folders from Excel files. This step works.
Description of the problem
After using any combination of any technique to wait for Power Query to complete the upgrade, as described in the “Things I tried:” section, shown below. A message box may be displayed, and any other code may be executed before the Power Query tables have finished updating according to the icon of the update indicator (spinner?) Displayed in the Queries and Connections panel.
An exception to the above statement is the OnTime method of the Application class, shown in the Code section below, which does not appear to interrupt polling to update the power request. However, the problem with this method is that it uses a hard-coded amount of time to pause the VBA code, and this will not always work, because the size, amount and duration of the requested data will change over time.
What I tried:
- I read all of StackOverflow (and other site resources) that talk about using the DoEvents, BackgrgoundQuery = False, and CalculateUntilAsyncQueriesDone methods and properties.
- , ( ).
- Do Until/While Loops .Refreshing = True/False QueryTable, .
- BackgroundQuery Excel (menubar → Data → Connections → Properties) 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 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 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 backgroundquery property to false.
Case 2 'If its an ODBC connection the,
.ODBCConnection.BackgroundQuery = False 'Set it 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:
'/questions/292686/wait-until-activeworkbookrefreshall-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". , "Linit.ini_Setup_Project" ":" .
- , "Linit.gvTbl_ZZZZZ" - , . , , "zzzz" Power Query Excel. , , .
- - , Power Query , Excel , ?
- , - , , , , - , , ? ( , , , ).
- - VBA, , ?
- Excel, - , # Python?
- - , , ? , .
.
- , , -, , , .