Forum Discussion
Refresh All button, multiple Query Tables, and VBA
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.
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.
- 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.Option Explicit Private Sub Workbook_Open() Selection.QueryTable.Refresh BackgroundQuery:=False 'Worksheets(?).Range(?).querytable.refresh backgroundquery:=false (alternate code) Call YourMacro End SubI'm not sure if that's what's meant...but it doesn't hurt to send :))
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:
https://www.excelandaccess.com/create-beforeafter-query-update-events/
https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-handling-events-indefinite-sources
Thoughts?
- 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.- 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?
