I need help with QueryTable Events (AfterRefresh)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have searched discussions groups and googled til I can't take it no more.
I have been trying to get the qtQueryTable_AfterRefresh with no success.
Here is my code....

ThisWorkbook contains the following:

Option Explicit
Dim QT As ClsModQT
Dim wsQuery As Worksheet
Private Sub Workbook_Open()

Set QT = New ClsModQT
Set wsQuery = Sheets("HD")
QT.InitQueryEvent wsQuery.QueryTables(1)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.ThisWorkbook.RefreshAll
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Class Module ClsModQT contains the following....

Sub InitQueryEvent(QT As Object)
Set qtQueryTable = QT
End Sub

Private Sub Class_Initialize()

End Sub

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)

Dim wsCurrent As Worksheet
Dim rngUsed As Range
Dim strFormula As String

' Refresh Age Column on HD Worksheet

Set wsCurrent = Application.Worksheets("HD")
Set rngUsed = GetUsedRange(wsCurrent)

For r = 2 To rngUsed.Rows.Count
strFormula = "=NOW()-G" & r
rngUsed.Cells(r, 8).FormulaR1C1 = strFormula
Next r

' Refresh Age Column on CHG Worksheet

Set wsCurrent = Application.Worksheets("CHG")
Set rngUsed = GetUsedRange(wsCurrent)

For r = 2 To rngUsed.Rows.Count
strFormula = "=NOW()-G" & r
rngUsed.Cells(r, 8).FormulaArray = strFormula
Next r

Set wsCurrent = Nothing
Set rngUsed = Nothing

End Sub

The afterrefresh never fires. What am I doing wrong?

Thank you in advance for any assistance.

Regards, Dean.
 
Hi Dean,

In your class module code you don't show the declaration for the
qtQueryTable variable. It should be in the general declaration section at
the top of the class module and look something like this:

Private WithEvents qtQueryTable As Excel.QueryTable

The key feature of the declaration being the inclusion and proper location
of the WithEvents keyword.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm
 
One important item you omitted: Where and how do you Dim your qtQueryTable
variable? It needs to be defined WithEvents in order to use the event
procedures - can't tell whether you did this or not.
 
=?Utf-8?B?RGVhbiBIaW5zb24=?= said:
I have searched discussions groups and googled til I can't take it no more.
I have been trying to get the qtQueryTable_AfterRefresh with no success.
.
Click to expand...

I know this is very old, but just in case others are still struggling with this problem. If the underlying query that creates the QueryTable object in question has the "Enable Background Refresh" option enabled, then no amount of cajoling will get the afterrefresh event to fire. Either turn off the setting in the query, or set the correspondiing property on the QueryTable object to FALSE before causing a refresh, and it should work.

Bear
 
Back
Top