Querytables refresh gives Subscript out of Range Error
 

In Excel 2007 I have the very simple code (abridged for clarity):

Private Sub cmdRefreshRMTable_Click()
With Workbooks("RM_Master.xls").Worksheets("ESC_RM_Cost ")
.QueryTables(1).Refresh BackgroundQuery:=False
End With
End Sub

that gives me a subscript out of range error on the refresh method.
The workbook name is spelled correctly, the worksheet name is spelled
correctly and there is only one query table in the entire workbook.

In Excel 2003 this same code works properly.

What am I missing?

Answer: Querytables refresh gives Subscript out of Range Error
 

It's possible that the issue is related to the way Excel 2007 handles query tables. Here are a few things you can try:

1. Check the query table index: In Excel 2007, the index of the first query table is 0, not 1. So, if you only have one query table, you should use .QueryTables(0) instead of .QueryTables(1).
2. Check the connection string: Make sure that the connection string for the query table is correct and up-to-date. If the connection string is invalid, the refresh method may fail.
3. Check the data range: Make sure that the data range for the query table is correct and up-to-date. If the data range is invalid, the refresh method may fail.
4. Try using a different method to refresh the query table: Instead of using the Refresh method, you can try using the BackgroundQuery property to refresh the query table. Here's an example:
   
   
   Formula:

   ---

   With Workbooks("RM_Master.xls").Worksheets("ESC_RM_Cost").QueryTables(0)
      .BackgroundQuery = True
      .Refresh
   End With 


   ---