如何在VBA中的Excel 2016中引用和刷新QueryTable [英] How to reference and refresh a QueryTable in Excel 2016 in VBA

问题描述

我正尝试刷新有关单元格更改的查询，但是我不知道如何引用该查询.

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

我的代码:Sheets("Roster Query").QueryTables(0).Refresh

只是出现以下错误:

运行时错误"1004":

Run-time error '1004':

应用程序定义或对象定义的错误

Application-defined or object-defined error

我有一个名为"Roster Filter"的工作表，其中包含要刷新的查询表.如何获取该QueryTable并刷新它?

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

也尝试过:

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

这不会出错，但是查询不会刷新.

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

推荐答案

在表出现之前，查询表是Excel早期版本的遗留物.甚至不知道如何在Excel 2007+中创建一个.

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+.

如果通过数据/获取外部数据功能区菜单添加了QT，则实际上添加的是ListObject.

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

我在Sheet1上对此进行了测试，并添加了一个简单的查询-Excel为我创建了ListObject:

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

在立即窗格中，我得到了以下结果:

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.

解决方案是改为刷新ListObject:

Sheet1.ListObjects(1).Refresh 'works

您也可以通过ListObject访问基础 QueryTable对象:

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

?Sheet1.ListObjects(1).QueryTable.CommandText 'gives you the query

这篇关于如何在VBA中的Excel 2016中引用和刷新QueryTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！