Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help with QueryTable Events (AfterRefresh)
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help with QueryTable Events (AfterRefresh)
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 "Dean Hinson" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help with QueryTable Events (AfterRefresh)
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. "Dean Hinson" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC AfterRefresh Event and Shared Workbook | Excel Worksheet Functions | |||
Add QueryTable in code | Excel Programming | |||
Can't change name of querytable. | Excel Programming | |||
Querytable | Excel Programming | |||
MS QUERY w/out querytable | Excel Programming |