Querytable Class Module

[Stevek]

Hello All,

This is my first time trying create a Class Module with events. I cannot
seem to get the AfterRefresh Event to fire.
Any help would be appreciated. TIA

Steve

Here's what my Class Module (ClsModQt) looks like:
===============================================================================
Public WithEvents qtQueryTable As QueryTable

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

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)
MsgBox ("Refreshed")
End Sub
===============================================================================

Here what my Sub looks like:
===============================================================================
Private Sub CreateQueryTable(ByVal SQLString, ByVal SheetName)

Dim Destination As Range
Dim qt As QueryTable
Dim MyQt As New ClsModQT

Set Destination = Worksheets(SheetName).Range("A5")
Worksheets(SheetName).Range("A5:Z400").ClearContents

' --> Delete the existing names on the sheet <--
For Each nm In Worksheets(SheetName).Names
nm.Delete
Next

' --> Delete the existing query tables on the sheet <--
For Each qt In Worksheets(SheetName).QueryTables
qt.Delete
Next

MyQt.InitQueryEvent _
qt:=Worksheets(SheetName).QueryTables(1)

ConnString = "ODBC;DSN=MyDSN;UID=MyID;PWD=MyPWD;Database=MyDb;"

With Worksheets(SheetName).QueryTables.Add(Connection:=ConnString,
Destination:=Destination, Sql:=SQLString)
.Name = SheetName
.RefreshStyle = xlInsertDeleterows
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh
End With

End Sub

===============================================================================

 

[Leith Ross]

Hello All,

This is my first time trying create a Class Module with events. I cannot
seem to get the AfterRefresh Event to fire.
Any help would be appreciated. TIA

Steve

Here's what my Class Module (ClsModQt) looks like:
===============================================================================
Public WithEvents qtQueryTable As QueryTable

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

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)
MsgBox ("Refreshed")
End Sub
===============================================================================

Here what my Sub looks like:
===============================================================================
Private Sub CreateQueryTable(ByVal SQLString, ByVal SheetName)

Dim Destination As Range
Dim qt As QueryTable
Dim MyQt As New ClsModQT

Set Destination = Worksheets(SheetName).Range("A5")
Worksheets(SheetName).Range("A5:Z400").ClearContents

' --> Delete the existing names on the sheet <--
For Each nm In Worksheets(SheetName).Names
nm.Delete
Next

' --> Delete the existing query tables on the sheet <--
For Each qt In Worksheets(SheetName).QueryTables
qt.Delete
Next

MyQt.InitQueryEvent _
qt:=Worksheets(SheetName).QueryTables(1)

ConnString = "ODBC;DSN=MyDSN;UID=MyID;PWD=MyPWD;Database=MyDb;"

With Worksheets(SheetName).QueryTables.Add(Connection:=ConnString,
Destination:=Destination, Sql:=SQLString)
.Name = SheetName
.RefreshStyle = xlInsertDeleterows
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh
End With

End Sub

===============================================================================

Hello Stevek,

Doing this is confusing. FIrst you need to name your Class module
"QueryTableClass". In the VBE display the Class module code. Bring up
the Properties window (type F4 if it isn't visible). Type
"QueryTableClass" in to the right of the "Name" property. Your Class
module code should look like this...
======================================================================
Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

MsgBox "Refreshed"

End Sub
======================================================================


Next you need to create a separate Standard module to initialize your
New Class. Place this code in your Workbook_Open() event. This code
needs to run before the start of the Query. Here is the code...
======================================================================
Dim QT1 As New QueryTableClass 'This is in the Declarations section
of the module

Sub Initialize_QT1()

' Note: If you have more than one Query Table on your Worksheet
change the index
' for QueryTables(n) to match.

Set QT1.qt = ThisWorkbook.Worksheets("Sheet1").QueryTables(1)

End Sub
======================================================================

Sincerely,
Leith Ross

 

[Stevek]

Thank you Leith,

I did exactly as you suggested. My class now is QueryTableClass. The code in
the class is as you suggested. And in the workbook_open() event I added the
suggested code.
I would expect that each time the query table refreshes a Message Box would
popup with the text "Refreshed". But it does not.

 

[Leith Ross]

Thank you Leith,

I did exactly as you suggested. My class now is QueryTableClass. The code in
the class is as you suggested. And in the workbook_open() event I added the
suggested code.
I would expect that each time the query table refreshes a Message Box would
popup with the text "Refreshed". But it does not.

Hello Stevek,

If you can email your workbook, I will look over the code for you. You
can email me at (e-mail address removed).

Sincerely,
Leith Ross