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

 

[MichaelSchulz]

Basically, you would need to use RibbonX XML code to assign your own macro to the RefreshAll control. So when you click on RefreshAll, your code would run instead. Your code would be written so that it first refreshes all tables, and then continues with your other code.

Just to give you an idea of whats involved, first you would need to download the Custom UI Editor to help with adding the XML code to your workbook...

VBA Ribbon - Microsoft Download

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

Then you would add the neccessary XML code and VBA code to your workbook, as per the following instructions...

How to Customize the Excel 2016 Ribbon with XML - dummies

In some situations, you may want to modify the Excel Ribbon automatically when a workbook or add-in is opened. Doing so makes it easy for the user to access you

www.dummies.com

At first glance, it might look like it's a little too complicated. But it's actually not as bad as it looks. If you would like to give it a try, let me know and I can provide you with the necessary XML code and VBA code.

Why can't we use the Refresh All button? What mean is that the Refresh All does refresh the tables—isn't there a way to 'detect' when all the tables have completed the refresh, maybe using the AfterRefresh event?

 

[Domenic]

You could do it using a class module. But the AfterRefresh event will get triggered after each successive refresh. So the problem, though, how to run your code after all tables have been refreshed.

 

[MichaelSchulz]

You could do it using a class module. But the AfterRefresh event will get triggered after each successive refresh. So the problem, though, how to run your code after all tables have been refreshed.

What about an array variable sized equal to the number of Query Tables in the workbook that has an element populated with each refresh. That array variable could be checked at each AfterRefresh event to see if all elements have been populated. When all the Query Tables have had a successful refresh, then all the array elements would be populated, and that could then trigger the next block of code to run.

Thoughts?

 

[Domenic]

I had thought of a similar solution using a dictionary object instead. But I think the problem might be that these tables can be refreshed indiviually at different times. And so the last table could be refreshed sometime later than the first one. And so in this case your macro would run when only the last table is updated. I guess one could check that all tables were refreshed within the last certain amount of minutes. But personally, I don't think this approach is all that great because of this timing issue.

By the way, just curious, any reason why you're not kean on adding a new button on the ribbon to do what you want?

 

[MichaelSchulz]

I had thought of a similar solution using a dictionary object instead. But I think the problem might be that these tables can be refreshed indiviually at different times. And so the last table could be refreshed sometime later than the first one. And so in this case your macro would run when only the last table is updated. I guess one could check that all tables were refreshed within the last certain amount of minutes. But personally, I don't think this approach is all that great because of this timing issue.

By the way, just curious, any reason why you're not kean on adding a new button on the ribbon to do what you want?

One: trying to keep things super simple for the users in question.
Two: I want to expand my understanding of what can be done and why things cannot be done. It just seems that since the Refresh All does refresh all the Query Tables that there ought to be a way to 'detect' when all the tables have finished and use that as the trigger for the next block of code.

 

[Domenic]

Okay, I'll have a look at this later this evening when I get a chance.

 

[MichaelSchulz]

I have thought but…is there a way of detecting the BeforeRefresh event of any Query Table that's in the workbook? Worded another way, if there are five Query Tables and if the user clicks to refresh just one table instead of the Refresh All, is there a way of detecting the BeforeRefresh event without it being explicitly tied to a particular table?

 

[Domenic]

Whether it's the BeforeRefresh event or the AfterRefresh event, they're both tied to the particular table being refreshed.

Anyway, I've taken another look at it. As I thought, the ability to refresh tables individually will make it much more challenging.

 

[MichaelSchulz]

Whether it's the BeforeRefresh event or the AfterRefresh event, they're both tied to the particular table being refreshed.

Anyway, I've taken another look at it. As I thought, the ability to refresh tables individually will make it much more challenging.

There must be some way to detect that a Query Table is being refreshed, whether that's from a BeforeRefresh/AfterRefresh event or something to do with the connection. There must be some way to know that a refresh is/has taking/taken place.

 

[MichaelSchulz]

I have found something that works but now I am not sure if it's a good approach.
Or if there is a better approach.

The basic idea is to create a custom class module that is utilized for each Query Table found in the workbook and add each instance of the custom class to a collection. The collection is set WithEvents.
The Workbook_Open event triggers the code that finds all the Query Tables in the workbook and adds each to the collection. I added a dynamic string array variable that is sized equal to the number of Query Tables found.

Then when there is a successful AfterRefresh event, the name of the Query Table is added to the array variable. With each successful AfterRefresh event another Query Table name gets added to the array. When the array is fully populated, that should mean all the tables have completed a successful refresh.

And that triggers the next block of code to run.

Here are the articles I found to provides the basis of this idea:
Create Before/After Query Update Events
VBA - Handling events from an indefinite number of event sources

Thoughts?