AfterRefresh event i VBA Excel 2007

Hej eksperter!

Jeg har nedenstående VBA kode, som køre i Excel 2003. Koden opdatere alle queries i Excel filen og først når sidste query er opdateret - så beregnes der!

Dataerne hentes ind via ODBC.

Mit problem er at dette tilsyneladen ikke virker i Excel 2007. Jeg kan konstatere at når jeg laver en qyery i Excel 2007 - så laves den som en tabel (eller liste) - dette er ikke tilfældet i 2003. Excel 2007 anvender ListObject sammen med QueryTable og jeg tror det er her "hunden ligger begravet"?

Problemet er at med nuværende kode findes der ikke nogen QueryTables, men kun ListOjects. Og det virker ikke blot at skift alle QueryTables ud med ListOjects!?

Da jeg ikke ligefrem er nogen haj til det her, håber jeg at I eksperter kan give mig et skub i rigtige retning.



+++++ThisWorkbook+++++

Private Sub Workbook_Open()


    '...You can initialize the event in a module by calling the event.
    '...Initializing query AfterRefresh event.
    Call RunInitQTEvent

  '...Refresh all queries in workbook.
    For Each Worksheet In ThisWorkbook.Worksheets
        For Each QueryTable In Worksheet.QueryTables
            QueryTable.Refresh
        Next
    Next

End Sub

+++++Class Modul+++++

'...Before you can use events with the QueryTable object,
'...you must first create a new class module and declare
'...a QueryTable object with events.
Public WithEvents qtQueryTable As QueryTable


'...After you’ve declared the new object by using events,
'...it appears in the Object drop-down list box in the class module.
'...Before the procedures will run, however, you must connect the declared object
'...in the class module to the specified QueryTable object.
Sub InitQueryEvent(QT As Object)
    Set qtQueryTable = QT
End Sub


Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)

    If Success = True Then
        '...Query completed successfully

        Application.Calculate

        'Call Procedure here

    Else
        '...Query failed or was cancelled
        MsgBox "Calculation failed!"
   
    End If
     
End Sub


+++++Modul+++++

'...After you run this initialization procedure,
'...the object you declared in the class module points to the specified QueryTable object.

Dim clsQueryTable As New ClsModSOPQT


Sub RunInitQTEvent()
'...AfterRefresh event uses the last query that is refreshed.
   
    Dim i As Integer
    Dim iNumberOfWorksheets As Integer

    Dim iLastSheet As Integer
    Dim iLastQueryTable As Integer

    iNumberOfWorksheets = Worksheets.Count

MsgBox ThisWorkbook.Sheets(1).ListObjects.Count
   
    '...Look in all worksheets in workbook
    For i = 1 To iNumberOfWorksheets
           
            '...Look for all queries in worksheet
            If ThisWorkbook.Sheets(i).QueryTables.Count > 0 Then
                iLastSheet = i
                iLastQueryTable = ThisWorkbook.Sheets(i).QueryTables.Count
            Else
                '...Do nothing
            End If

    Next

'Her går det galt!!!

    clsQueryTable.InitQueryEvent _
        QT:=ThisWorkbook.Sheets(iLastSheet).QueryTables(iLastQueryTable)


End Sub


Private Sub RefreshQueryTables()
    If ActiveSheet.QueryTables.Count > 0 Then
        Dim i As Integer
        For i = 1 To ActiveSheet.QueryTables.Count
            If Not ActiveSheet.QueryTables(i).Refresh() Then
                MsgBox ("Refresh of query table " & ActiveSheet.QueryTables(i).Name & _
                    " failed.")
            End If
        Next i
    Else
        MsgBox ("This worksheet contains no query tables.")
    End If
End Sub