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.

5 thoughts on “Create Before/After Query Update Events

    • Well you could really query anything you want actually! A straight-up web query will need a URL. Once you get that it’ll be previewed in the dialog box and waiting for you to tell it which part of the web page you’re wanting to return for your query. So basically any valid URL on the internet will work!

      Another great tool to do web querying from is Power Query. Based on the language M, it’s a super powerful tool which lets you get data from many sources – the web being one of them. One of the things I did when I first got the tool was check my Facebook and Twitter accounts for scraping the data into Excel. Not too meaningful, but really fun to do!

      One thing I’m working on now for someone is to query all of the snowfall data for specific monitored sites. As always there’s 101 ways to skin this cat, and this is just one of them, but it’s a good example of getting current information from the web into Excel. In this specific case we used an after query update event to parse out specific data for summarization.

  1. Thanks, but it doesn’t work for me. My data connection is as follows : I use “Existing Connections” to set up a table. A right-click on the table and “Edit Query” brings up the Connection and Command Type/Text for my SQL “EXEC spMyQuery”….. but adding your code has made no difference. The initialising is being called, but Data..Refresh All doesn’t do anything different to what it used to. I’m still looking for an event I can hook into to run code after this refresh! Thanks.

  2. .. the For Each (For Each QT In WS.QueryTables) doesn’t loop over anything for me. My data connection method doesn’t seem to appear in ThisWorkbook.Worksheets.QueryTables ?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s