0

I've read lots of other posts (particularly this) and even tried to replicate a few and I don't understand what's wrong.

I'm trying to get a function to run after the spreadsheet has been refreshed. It seems that I have to mess around with query tables, though if there is some way that I can avoid that, please tell me. I've attached what I think are the relevant code snippets (though I could have missed some).

The error I get is runtime eror: '9' Subscript out of range

code in "this workbook"

    Dim qtevent As qtClass

    Private Sub Workbook_Open()

    Set qtevent = New qtClass
    Set qtevent.HookedTable = ThisWorkbook.Worksheets("REFRESH SHEET").ListObjects(1).QueryTable
    MsgBox "Qt Events Run"

    End Sub

Code in class qtClass

    Option Explicit

    Private WithEvents qt As Excel.QueryTable
    Public Property Set HookedTable(q As Excel.QueryTable)
        Set qt = q
    End Property

    Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "qt_AfterRefresh called sucessfully."
    If Success = True Then
        MsgBox "If called succesfully."
    End If

    End Sub
Community
  • 1
  • 1
NYoung
  • 157
  • 1
  • 14
  • The most likely cause of that error is that there is no sheet called "REFRESH SHEET" in the workbook containing the code, or there isn't a Table on that sheet. – Rory Feb 23 '16 at 16:21
  • Thanks Rory. There is a sheet called refresh sheet. There is a connection on that sheet, though I don't know if that is a table - is there a way to look at the "listObjects" to see what is there? Also is there a way to do afterrefresh events without needing to deal with tables? – NYoung Feb 24 '16 at 11:22
  • What is the connection to? If you select a cell in the result data, do you see the Table Tools tab on the Ribbon? If not, it's not a table. – Rory Feb 24 '16 at 11:47
  • The connection goes to a table on a website which fills with data which the excel sheet copies. Having checked, the table tools tab does not appear. To be clear, all I want to do is have it run a macro when the connection is refreshed. I've only been involving query tables because that is what everyone is talking about. If there is a simpler way, I'd love to do that. – NYoung Feb 26 '16 at 08:52
  • Ahh, in that case can you try using: `Set qtevent.HookedTable = ThisWorkbook.Worksheets("REFRESH SHEET").QueryTables(1)` instead? – Rory Feb 26 '16 at 09:26
  • Yes, that makes it work. Thank you so much. – NYoung Feb 26 '16 at 15:06

0 Answers0