Forum Discussion

MichaelJSchulz's avatar
MichaelJSchulz
Copper Contributor
May 27, 2022

Refresh All button, multiple Query Tables, and VBA

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 w...
Macros and VBA
NikolinoDE's avatar
NikolinoDE
Gold Contributor
May 30, 2022

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 :))

MichaelJSchulz's avatar
MichaelJSchulz
Copper Contributor
May 30, 2022

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?

Resources