How to run VBA code after the "Refresh All" is pressed

Question

I have created a SQL view on the AS/400 called SHIPVIEW and I then am connecting to this view from Excel using an ODBC connection.

I have also verified that if the data in the view changes and I press the "Refresh All" in Excel under the Data tab that the data in Excel is also being updated correctly.

I now need to create some VBA code that when the workbook is opened or when the "Refresh All" is selected, that it kicks off the VBA code to do some calculations on the data that was just imported.

I found the below link, but I didn't have any luck getting it to work.

https://stackoverflow.com/questions/29433281/refresh-all-pivot-tables-in-excel-workbook-using-vba-macro?r=SearchResults&s=2|99.4596

I'm new to VBA, so any answers would be welcomed!

Answer 1

Presumably the view is being dumped onto a worksheet; Excel creates a refreshable QueryTable object for it, and the QueryTable object exposes BeforeRefresh and AfterRefresh events you can handle.

This is done by declaring a WithEvents object variable of the appropriate type in a class module (WithEvents is illegal in a standard procedural module), and then selecting the WithEvents variable in the top-left code pane dropdown, then the event we want to handle in the top-right dropdown:

Assuming the table exists in the workbook at compile-time (i.e. it's not being created from scratch by some other VBA code), you could have something like this in the ThisWorkbook module:

Option Explicit
Private WithEvents QT As QueryTable

Private Sub QT_AfterRefresh(ByVal Success As Boolean)
    If Success Then
        'do stuff
    End If
End Sub

Private Sub Workbook_Open()
    Set QT = Sheet1.QueryTables(1) 'adjust accordingly
End Sub

If the QueryTable is spawned at run-time by other code, then you could make QT public, and assign it from the code that's responsible for creating it.

Note that these QueryTable events will fire regardless of how the QT was refreshed: whether it was a right-click to refresh that query, or a click on "refresh all", makes no difference.