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]

What I'm saying is the code I posted does work with the Refresh All button but for only one table.

In that prior project, the user clicks the Refresh All button on the ribbon, the code detects when that one table has finished the refresh and then runs the rest of the code.

But the code---as it is---seems limited to one table. At the very least, I can't seem to think of the way to extend it to all tables.

 

[johnny51981]

Good luck to you.

 

[MichaelSchulz]

I think I may have a solution to this but I want to ask this again with a clearer explanation before I know whether this is a good answer or not.

 

[MichaelSchulz]

I have an Excel workbook containing multiple Query Tables.

To refresh the data in all the Query Tables, I want the User to click the Refresh All button on the Ribbon.

I have a block of VBA code I want to run automatically once all the tables have completed being refreshed.

Over time, the number of Query Tables within the workbook may change, e.g. a new table may get added and/or a table may get removed.



How can this be accomplished?

 

[smozgur]

I think I may have a solution to this but I want to ask this again with a clearer explanation before I know whether this is a good answer or not.

When you come up with a solution and post it, then it is perfectly fine to mark your own post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[Peter_SSs]

@MichaelSchulz

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In this case I have moved your 'clearer explanation' new thread post to your existing thread on this topic.

 

[Domenic]

While it's possible to assign your own macro to a built-in control, such as RefreshAll, I would suggest that you create a custom button within a custom group in the built-in Table Design tab. Then, once the button is clicked, it will refresh all and run your macro. See the attached image.

Also, if you're going to need this for a number of users, you can create an add-in and distribute it to them.

Would this work for you?

 

[MichaelSchulz]

When you come up with a solution and post it, then it is perfectly fine to mark your own post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

While it's possible to assign your own macro to a built-in control, such as RefreshAll, I would suggest that you create a custom button within a custom group in the built-in Table Design tab. Then, once the button is clicked, it will refresh all and run your macro. See the attached image.

View attachment 65771

Also, if you're going to need this for a number of users, you can create an add-in and distribute it to them.

Would this work for you?

I am curious to know how to do this with the user clicking the Refresh All button.

 

[MichaelSchulz]

I am curious to know how to do this with the user clicking the Refresh All button.

Furthermore, how to know when all the tables have completed being refreshed and use that as a trigger for the next block of code

 

[Domenic]

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.