Run VBA after ALL tables have refreshed

[MichaelSchulz]

I want to have a block of VBA code run after all the tables in the workbook have refreshed:

1. User clicks the Refresh All button which causes
2. All tables in the workbook to be refreshed
   1. four tables, each on a separate sheet
   2. tables are refreshed via Power Query
3. Once all tables have completed the refresh, the VBA code runs

I have accomplished this in a different project using WithEvents and setting a QueryTable variable but that was only one table being refreshed. I just can't seem to see the logic to extend that approach to encompass multiple tables.

The code used for one table, set in ThisWorkbook:

Option Explicit
Private WithEvents QT as QueryTable

Private Sub Workbook_Open()
       Set QT = Sheet1.ListObjects(1).QueryTable
End Sub

Private Sub QT_AfterRefresh(ByVal Success As Boolean)
        If Success Then
              [I][block of code to be run after refresh completes][/I]
        End If
End Sub

 

[johnny51981]

Hi @MichaelSchulz
In each of the queries, is the 'Enable background refresh" unchecked? In case you don't know where it is at, right click on the query in the Queries & Connections pane and select Properties, it will be right there at the top of the Usage tab.

This will ensure that the refresh is complete prior to the next step to take place. I have yet to find a way to allow the queries to refresh simultaneously before the next piece of vba is performed.

 

[MichaelSchulz]

Yes, they are unchecked.

However, in my prior project the 'trigger' for the code to run was when one specific table had completed the refresh.

Now, I want the trigger to be when all the tables have completed the refresh. Unchecking the background refresh is helpful but won't I need someone more explicit in the code to ensure that it will run at the right time?

 

[MichaelSchulz]

For example, let's say I set the code to run after Table4 is refreshed. The user clicks the Refresh All button. Table4 finishes refreshing but Table2 is still in the process of refreshing.

When Table4 has finished refreshing, that would trigger the code to run. However, since Table2 hasn't finished refreshing, the results are not what is wanted.

 

[johnny51981]

What code are you using to initiate the query refresh?

The codes that I am aware of are either ActiveWorkbook.RefreshAll OR ActiveWorkbook.Connections("Query - Name of Query").Refresh, both of which, when "Enable background refresh" is unchecked, does not allow for simultaneous refreshing.

 

[MichaelSchulz]

No code is being used to initiate the query refresh; the user clicks the Refresh All button on the Ribbon. That action is what initiates the refresh of all the tables.
I want my code to run after all the tables have completed the refresh.

 

[johnny51981]

Ahh ok. So that's why.
So you'll have to set up the VBA that does the Refresh, rather than using the built in function. I don't know why they would be different, but I have run into that same thing in the past.

Here is what you may want to try

Sub RefreshData()

    Dim answer1 As Integer
    answer1 = MsgBox("Would you like to refresh all the Data?", vbQuestion + vbYesNo + vbDefaultButton2, "Refresh All Data")
    If answer1 = vbYes Then
    
    ActiveWorkbook.Connections("Query - Table1").Refresh
    ActiveWorkbook.Connections("Query - Table2").Refresh
    ActiveWorkbook.Connections("Query - Table3").Refresh
    ActiveWorkbook.Connections("Query - Table4").Refresh
'Repeat for all queries you are looking to refresh.
    
'Call the VBA that runs the next steps after all tables are complete
    
    answer1 = MsgBox("Data Refresh Successfull!", vbOKOnly)
    Else: answer1 = MsgBox("Data Refresh Cancelled!", vbOKOnly)
    End
    End If
End Sub

 

[MichaelSchulz]

It can be done with just one table—I have done it— seems there ought to be some way of doing it with multiple tables.

 

[MichaelSchulz]

Why can the approach work with one table but cannot be extended to more than one table?

Could there be some way to treat the QT variable as a kind of dynamic array variable so it could hold references to multiple tables?

Or some method to record in memory that a table has been refreshed such that when the count of refreshed tables equals the total number of tables in the workbook that acts as a trigger to run the next bit of code?

 

[johnny51981]

The built in function of Refresh All in the ribbon just does not allow for that. You will need to program a refresh vba in the specific order you are needing and then have your next code begin after that ends.