CODEWITHSUNDEEP
vbaexceleventstriggers
browning0
browning0

Reputation: 911

Refresh All not triggering QueryTable's BeforeRefresh event - why?

As stated in a title: I've found out that clicking "Refresh All" button on a ribbon doesn't trigger QueryTable's BeforeRefresh event. Why is it so? Is there a way to change this behavior?

The strange thing is that AfterRefresh event of the very same QueryTable is triggered perfectly!

To analyze this behavior I've created two tables in two worksheets:

  1. Simple, unlinked table called Source
  2. Table called Destination linked to Source table. Connection was created using Microsoft Query and Excel Files as Data source.

Then, I've created TestClass as follows:

Option Explicit

Private WithEvents qt As QueryTable

Public Sub Init(pQt As QueryTable)
    Set qt = pQt
End Sub

Private Sub qt_BeforeRefresh(Cancel As Boolean)
    MsgBox "BeforeRefresh"
End Sub

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

Finally, I've created, initialized and stored an instance of TestClass.

Right-clicking Destination table and choosing "Refresh" gives expected results: MsgBox is displayed twice, confirming that both Before- and After- Refresh events were triggered.

However, clicking "Refresh All" on ribbon results in only one MsgBox being displayed: AfterRefresh one.

I've prepared a minimal Excel file that reproduces described behavior. It is available for download here: RefreshAllNotTriggeringBeforeRefresh.xlsm

EDIT 1: In response to Rik Sportel's question on how TestClass instance is being created and initialized.

Here's a relevant part of ThisWorkbook object:

Option Explicit

Private Destination As New TestClass

Private Sub Workbook_Open()
    Destination.Init WS_Destination.ListObjects("Destination").QueryTable
End Sub

Where WS_Destination is a value for a (Name) property of a worksheet containing Destination table.

As one can see, Destination is a private field, but it seems that garbage collector doesn't care: I can right-click refresh Destination table as many times as I want and both Before- and After- MsgBoxes always pop up. Whereas, Refresh All never (not even once) triggers BeforeRefresh event, yet always triggers AfterRefresh one.

Upvotes: 2

Views: 1831

Answers (1)

Rik Sportel
Rik Sportel

Reputation: 2679

Option Explicit
Public tc as TestClass
Sub Test()
    Set tc = New TestClass
    tc.Init Worksheets("SomeSheet").ListObjects(1).QueryTable
End Sub
  • The BeforeRefresh only triggers when you use the refresh option in the Table tab Excel GUI. When you use this one, both events trigger appropriately.
  • When you use the refresh option in the Query tab of the Excel GUI, only the second event triggers.

The reason is that the QueryTable does not have this BeforeRefresh event triggered when using the Query refresh option, is because you're not actually refreshing the QueryTable itself, but the underlying Query.

  • The BeforeRefresh event occurs before any refreshes of the query table. Here
  • The AfterRefresh event occurs after the query is completed or cancelled. Here

When you do:

Option Explicit
Public tc as TestClass
Sub Test()
    Set tc = New TestClass
    tc.Init Worksheets("SomeSheet").ListObjects(1).QueryTable
    Worksheets("SomeSheet").ListObjects(1).QueryTable.Refresh
End Sub

You'll see that both events trigger appropriately.

Edit: RefreshAll actually refreshes the queries in the workbook, not the querytables. The same answer applies.

In short: It's behaving as expected.

Upvotes: 4

Related Questions