Listen to refresh events in excel

Question

When the user does click on the "Refresh" / "Refresh All" button, excel seems to just call the refresh method for each (or selected) QueryTable(s) in the Workbook. However, listening to BeforeRefresh and AfterRefresh events for QueryTable does not realy help me here, because I need to execute some stuff after all the QueryTables in the Workbook (respectively after all the selected QueryTables) are updated.

Is there a way to accomplish this? Maybe its possible to somehow to listen to a mouse click on that refresh button?

Answer 1

Actually it's how I intended to accomplish this in the first place. But there is a problem here. Lets say the total number of QueryTables is 10. And lets say the user just selected one QueryTable and then pressed "Refresh All". So, fist my algorithm would check for the number of selected QueryTables which is 1. As a result my calculations would start after just 1 refresh which is wrong.

In the meanwhile I've tried to access that "Refresh" button in Ribbon. But it didn't work out. For some reason my code doesn't do anything...

Public Class ThisAddIn
   Private Sub ThisAddIn_Startup() Handles Me.Startup
      AddHandler Globals.ThisAddIn.Application.WorkbookActivate, AddressOf OnWorkbookOpened
   End Sub

   Private refrBtn As Office.CommandBarButton

   Private Sub OnClick(ByVal Ctrl As Office.CommandBarButton, _
                  ByRef CancelDefault As Boolean)
      MsgBox("PLS WORK!")
   End Sub

   Private Sub OnWorkbookOpened(wb As Excel.Workbook)
      Try
         refrBtn = CType(wb.Application.CommandBars.FindControl(Id:=459), Office.CommandBarButton)
         AddHandler refrBtn.Click, AddressOf OnClick
      Catch ex As Exception
         MsgBox(ex.Message)
         MsgBox(ex.GetType)
         MsgBox(ex.StackTrace.ToString)
      End Try
   End Sub
End Class

Edit: I forgot my login data, so I've created a new account

Answer 2

I'm not using VSTO but I would investigate:

• Create a global variable (or workbook property) set to 0
• In each AfterRefresh event call a procedure
• This procedure increments the counter
• When the counter reaches the total number of QueryTables (or the total selected number), do what you need to do and reset the counter to 0.

You might also check the Success argument each time, so that your code might only run if the selected QTs were all successfully updated.