How to reference and refresh a QueryTable in Excel 2016 in VBA

vba excel excel-2016
Douglas Gaskell picture Douglas Gaskell · Aug 18, 2016 · Viewed 34.1k times · Source

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.

Answer

Mathieu Guindon picture Mathieu Guindon · Aug 18, 2016

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

Related questions

Can't find Microsoft Forms 2.0 Object Library or FM20.DLL

In my Excel 2016 project (Windows 7), I'd like to work with the clipboard: Dim DataObj As New MSForms.DataObject DataObj.GetFromClipboard But I've got this compiler Error at the first line of code: User-defined type not defined So I would like …

Read More
How to make Microsoft Web Browser object work in Excel 2016

When I try to insert it, I constantly get the "Cannot insert object" error. It seems to be a known issue with former Excel versions, but I couldn't find any support for 2016. Has anyone been able to make this work? …

Read More
How to call a function on ENTER key in Excel using vba

Google should provide me with ample examples but none of them seem to work What I want: Everytime the user presses, and then releases, the ENTER key, for my program to do do something (ie. create a MsgBox, or call …

Read More