How to use QueryTable.BeforeRefresh event?

dnagirl 0
Viewed 29

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: table excerpt

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?

powerquery vba excel
1 Answers

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.

useryPy710
Related