Refresh All button, multiple Query Tables, and VBA

[MichaelJSchulz]

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?

[#2]

@MichaelJSchulz 

[Makro] Bestimmte Power Query Abfrage Aktualisieren

Option Explicit
Sub Makro1()
Dim MyList As ListObject
Set MyList = Worksheets("Tabellenblat_Name").ListObjects("Tabellen_Name")
MyList.QueryTable.Refresh BackgroundQuery:=False
End Sub

NikolinoDE

[#3]

@NikolinoDE
I am not sure I understand how this subroutine would accomplish the goal. How does this make possible the triggering of my block of code after all tables have finished being refreshed?

[#4]

@MichaelJSchulz 

How to add a custom Ribbon tab using VBA?

I probably misinterpreted the translation, anyway here's some additional info

...if this doesn't help, please ignore my post.

If you allow, would recommend to inform more info about Excel version, operating system, storage medium. A file (without sensitive data) or photos with a more detailed explanation lead to greater success in getting a precise and accurate solution proposal.

[#5]

Something similar that I did. 

Right click the query > Properties 

Checkbox of Enable background refresh is always selected. You need to remove that.

When you do the refresh all with the vba code, the query is going refresh and wait until it is refreshed to move to the next line of code.

I think that if you are going to have more than 1 table, then you need to look for a code that loops over all the tables in the workbook and do that step of removing the enable background refresh.

@MichaelJSchulz 

[#6]

@alannavarro

The tables are not going to be refreshed with VBA code; the tables are going to be refreshed by the user clicking the Refresh All button.

[#7]

@MichaelJSchulz 

Is that what's not working?

Have you pressed the small file?...or can't you press the button at all?

However, a file would be appropriate to reproduce this.

[#8]

The Refresh All button does work. The Refresh All button does refresh the data in all the tables.
The question is how to have a block of VBA code run automatically once all the tables have finished being refreshed.

[#9]

why not use ActiveWorkbook.RefreshAll in your VBA code instead of going to Data > Refresh all?

at the end is the same result, just insert a button in which the user selects that button to run the macro.

[#10]

Why create a button to refresh all the tables when there is already button that does that? What advantage is there to doing that?
If I did do that, how would I trigger the next block of code to run automatically once all the tables have finished being refreshed?

[#11]

@MichaelJSchulz 

Option Explicit


Private Sub Workbook_Open()

Selection.QueryTable.Refresh BackgroundQuery:=False

'Worksheets(?).Range(?).querytable.refresh backgroundquery:=false (alternate code)

Call YourMacro


End Sub

I'm not sure if that's what's meant...but it doesn't hurt to send :))

[#12]

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?