How to use QueryTable.BeforeRefresh event?

Question

Microsoft documents a QueryTable.BeforeRefresh event for Excel. I'd love to use it to clear the values from some columns added to the table where a query loads. Here's a pic of the situation:

My problem is, I don't know where to put or how to name a VBA subroutine that I want to run every time my query refreshes, whether it refreshes automatically or because the user requests it to.

So, if my query is called prepareForMS, how do I hook into its BeforeRefresh event?

Ike · Accepted Answer · 2022-09-15 14:02:55Z

3

You can access the event like this - code is in the worksheets module:

Option Explicit

Private WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
MsgBox "after refresh"
End Sub

Private Sub qt_BeforeRefresh(Cancel As Boolean)
MsgBox "Before refresh"
End Sub


Private Sub Worksheet_Activate()
Set qt = Me.ListObjects(1).QueryTable
End Sub

qt gets initialized whenever you activate the according sheet.

answered Sep 15, 2022 at 14:02

Ike

13.4k6 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dnagirl Over a year ago

When you say "worksheets module" do you mean I need to put this on each worksheet? Or do you mean it goes on the Workbook?

Ike Over a year ago

It is possible to have classes for generic routines - but basically you have to instantiate the QueryTable on each sheet

dnagirl Over a year ago

It appears that WithEvents is only valid inside a class module: learn.microsoft.com/en-us/office/troubleshoot/excel/…

Ike Over a year ago

That's true - a worksheet module or a class module. That's why I wrote that you either have to place it in each worksheet module or create a class and create an instance per each worksheet.

Collectives™ on Stack Overflow

How to use QueryTable.BeforeRefresh event?

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related