Subscript out of range error in query table code

[dhouseknecht]

Hi All,

I've scavenged some code from a forum and I'm getting a subscript out of range error and I'm not sure what's going on. I'm really pretty new to VBA so go easy on me please. Also, sorry for the code snippets not being up to snuff in terms of formatting...I couldn't get the MrExcel add in working. Any help is greatly appreciated!!! Thanks in advance

When opening the spreadsheet, it throws a Error 9: Subscript out of range. And if I step through the code, it errors in MonitorQuery.

The code was found here:http://www.mrexcel.com/forum/excel-questions/670259-query-excel-updating-keeping-new-data.html

Code in ThisWorkbook

Private Sub Workbook_Open()​

'--Start monitoring for connection refresh events​

Call Sheets("Data").MonitorQuery​

End Sub​

Initial subset of Code in Sheet 1

Option Explicit​

​

Private WithEvents qt As QueryTable​

Private vStoredNotes As Variant, vKeysBefore As Variant​

Private sTableName As String, sKeyField As String​

Private sFirstNoteField As String, sLastNoteField As String​

​

Public Sub MonitorQuery()​

'--initialize module scope variables​

sTableName = "Data"​

sKeyField = "OBJECTID"​

sFirstNoteField = "STATUS"​

sLastNoteField = "COMMENTS"​

​

On Error GoTo ErrorHandler​

Set qt = Me.ListObjects(sTableName).QueryTable​

Exit Sub​

ErrorHandler:​

MsgBox "Error " & Err & ": " & Error(Err.Number)​

End Sub​

 

[RoryA]

Perhaps you could give a hint as to which line causes the error?