Create Before/After Query Update Events
By Zack Barresse
Returning a web query can be very handy. Unfortunately if you want to run any code when this refreshes, there’s no native way to do this. We can, however, create an event for this with a few short steps.
This will all be done inside the Visual Basic Editor (VBE). To get there click Visual Basic on the Developer tab (pictured below) or hit ALT + F11.
Step 1: Create a class module
In the VBE, right-click your project and select Insert | Class Module (pictured below). You can also do this from the Insert menu on the command bar.
Step 2: Rename class module
This is a very important step and is probably the most missed step when people re-create class module solutions.
For this example we will rename it (select the class module, press F4) to “clsQuery“.
The class name is referred to in code later on in this post. If you name it something different, be sure to adjust your code as well.
Paste the following code into the class module (double-click it in the PE to see the code pane):
Option Explicit Public WithEvents MyQuery As QueryTable Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean) If Success Then MsgBox "Query has been refreshed." End Sub Private Sub MyQuery_BeforeRefresh(Cancel As Boolean) If MsgBox("Refresh query?", vbYesNo) = vbNo Then Cancel = True End Sub
Your code should now look like the following:
Step 3: Create the standard module
Right-click your project in the PE and select Insert | Module.
Paste the following code (updated on 10-Feb-14, thanks to Andy Pope for pointing out a flaw!):
Option Explicit Dim colQueries As New Collection Sub InitializeQueries() Dim clsQ As clsQuery Dim WS As Worksheet Dim QT As QueryTable For Each WS In ThisWorkbook.Worksheets For Each QT In WS.QueryTables Set clsQ = New clsQuery Set clsQ.MyQuery = QT colQueries.Add clsQ Next QT Next WS End Sub
To initialize this code when the workbook opens, double click ThisWorkbook module and use the following workbook open code:
Option Explicit Private Sub Workbook_Open() Call InitializeQueries End Sub
That’s it, you now have query before/after events you can tie into. Note that if you make changes to the class module you’ll have to run the initialize code again.