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):
Public WithEvents MyQuery As QueryTable
Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean)
If Success Then MsgBox “Query has been refreshed.”
Private Sub MyQuery_BeforeRefresh(Cancel As Boolean)
If MsgBox(“Refresh query?”, vbYesNo) = vbNo Then Cancel = True
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!):
Dim colQueries As New Collection
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
To initialize this code when the workbook opens, double click ThisWorkbook module and use the following workbook open code:
Private Sub Workbook_Open()
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.