如何在 VBA 中的 Excel 2016 中引用和刷新 QueryTable
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39027286/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reference and refresh a QueryTable in Excel 2016 in VBA
提问by Douglas Gaskell
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
我的代码： Sheets("Roster Query").QueryTables(0).Refresh
Just errors out with:
只是错误：
Run-time error '1004':
Application-defined or object-defined error
运行时错误“1004”：
应用程序定义或对象定义的错误
I have a sheet named "Roster Filter" that has query table I want to refresh. How can I get that QueryTable and refresh it?
我有一个名为“名册过滤器”的工作表，其中包含要刷新的查询表。我怎样才能得到那个 QueryTable 并刷新它？
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.
这不会出错，但不会刷新查询。
回答by Mathieu Guindon
Query tables are a relic of older versions of Excel, before tableswere a thing. Not sure how to even create one in Excel 2007+.
在表格成为事物之前，查询表是旧版本 Excel 的遗物。不确定如何在 Excel 2007+ 中创建一个。
If you added your QT via the Data/Get External DataRibbon menu, what you added was actually a ListObject.
如果您通过Data/Get External DataRibbon 菜单添加了 QT ，那么您添加的实际上是一个ListObject.
I tested this on Sheet1, adding a simple query - Excel created the ListObjectfor me:
我在 上对此进行了测试Sheet1，并添加了一个简单的查询 - ExcelListObject为我创建了：
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.
该错误简直是令人发指的误导，仅此而已 - 它真的应该是一个index out of bounds。
The solution is to refresh the ListObjectinstead:
解决方案是刷新ListObject：
Sheet1.ListObjects(1).Refresh 'works
You can access the underlyingQueryTableobject via the ListObject, too:
您也可以通过访问底层QueryTable对象ListObject：
?Sheet1.ListObjects(1).QueryTable.CommandText 'gives you the query
回答by ARich
You're seeing an error because the .Itemmethod is base 1, not base 0
您看到错误，因为该.Item方法是基数 1，而不是基数 0
For example, this worked for me in Excel 2016:
例如，这在 Excel 2016 中对我有用：
Sheets("Roster Query").QueryTables(1).Refresh
So if you only have one QueryTable, it would be .QueryTables(1).
因此，如果您只有一个 QueryTable，它将是.QueryTables(1).
