6

I'm trying to refresh a query on a cell change, however I can't figure out how to reference the query.

My code: Sheets("Roster Query").QueryTables(0).Refresh

Just errors out with:

Run-time error '1004':

Application-defined or object-defined error

I have a sheet named "Roster Filter" that has query table I want to refresh. How can I get that QueryTable and refresh it?

Edit: Also tried:

For Each qt In Sheets("Roster Query").QueryTables
    qt.Refresh
Next

This does not error out, but the query is not refreshed.

Douglas Gaskell
  • 9,017
  • 9
  • 71
  • 128

2 Answers2

14

Query tables are a relic of older versions of Excel, before tables were a thing. Not sure how to even create one in Excel 2007+.

If you added your QT via the Data/Get External Data Ribbon menu, what you added was actually a ListObject.

I tested this on Sheet1, adding a simple query - Excel created the ListObject for me:

Excel creates a ListObject

In the immediate pane, I get these results:

?Sheet1.QueryTables.Count
 0
?Sheet1.ListObjects.Count
 1

And I can reproduce your exact same error:

Sheet1.QueryTables(0).Refresh 'runtime error 1004

The error is simply outrageously misleading, that's all - it should really be an index out of bounds.

The solution is to refresh the ListObject instead:

Sheet1.ListObjects(1).Refresh 'works

You can access the underlying QueryTable object via the ListObject, too:

?Sheet1.ListObjects(1).QueryTable.CommandText 'gives you the query
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • I'm using Power Query, does this not create a query table? The Excel documentation is fragmented all over the place, it's hard to make heads or tails of it. I'll have to look at the list object bit. Thanks. – Douglas Gaskell Aug 18 '16 at 21:33
  • @DouglasGaskell If you can type a *table formula* in it, it's a `ListObject`. A `QueryTable` (in Excel 97-2003) looked like a plain range, except you could refresh it. What do you get for `?Sheets("Roster Query").QueryTables.Count`? – Mathieu Guindon Aug 18 '16 at 21:35
  • That does the trick! Now to ask a question on how to prevent excel from hanging if there are functions updating when I try and refresh the table through VBS – Douglas Gaskell Aug 18 '16 at 22:04
  • @DouglasGaskell try `Application.Calculation = xlCalculationManual` (well, in VBScript that would be `yourExclAppInstance.Calculation = -4135`), assuming the Excel type library is late-bound. – Mathieu Guindon Aug 18 '16 at 22:09
  • 1
    @TylerH sure, but *index out of bounds* would point me to the off-by-one indexing much faster than error 1004.. anyway, all moot with ListObjects =) – Mathieu Guindon Mar 31 '20 at 19:44
4

You're seeing an error because the .Item method is base 1, not base 0

For example, this worked for me in Excel 2016: 

Sheets("Roster Query").QueryTables(1).Refresh

So if you only have one QueryTable, it would be .QueryTables(1).

ARich
  • 3,230
  • 5
  • 30
  • 56
  • That's rather awkward, given a collection class' default member is typically its `Item` property, which makes `.QueryTables(1)` completely equivalent to `.QueryTables.Item(1)`. What does `Sheets("Roster Query").QueryTables.Count` return? 1? Are you using Power Query? – Mathieu Guindon Aug 18 '16 at 21:51
  • @Mat'sMug I absolutely agree; it is rather awkward. And, to be clear, I don't think this approach is necessarily better than the one you provided, but it does work and I think solves the OP's issue. The answer to your question is 1, which is correct in my example setup. – ARich Aug 18 '16 at 21:57
  • I couldn't repro, but I'm on Excel 2010, so have an upvote... removing the explicit `.Item` call causes OP's error? (sorry to insist, I'm just kinda flabbergasted lol) – Mathieu Guindon Aug 18 '16 at 22:00
  • @Mat'sMug Sorry, forgot your second question; no, I'm not using Power Query. I created the QueryTable via VBA. Well, I thought removing the `.Item` caused the error, but no! Removing the `.Item` call is not causing an issue. But base 0 definitely is. I'll update my answer. – ARich Aug 18 '16 at 22:06