May 27 2022 11:48 AM
May 27 2022 11:48 AM
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?
May 27 2022 01:16 PM
Option Explicit Sub Makro1() Dim MyList As ListObject Set MyList = Worksheets("Tabellenblat_Name").ListObjects("Tabellen_Name") MyList.QueryTable.Refresh BackgroundQuery:=False End Sub
May 27 2022 01:21 PM
May 27 2022 01:36 PM
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.
May 27 2022 01:50 PM
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.
May 27 2022 02:18 PM - edited May 27 2022 02:42 PM
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.
May 28 2022 12:47 AM
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.
May 28 2022 11:26 AM
May 28 2022 09:21 PM
May 29 2022 09:38 AM
May 30 2022 04:03 AM
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 :))
May 30 2022 02:02 PM
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