问题描述
我正在使用VBA开发MS Excel 2013工具，其中涉及QueryTables的使用.
I am developing a MS Excel 2013 tool with VBA, which involves the use of QueryTables.
一个不便之处是访问Excel工作表中的现有查询表.当前，我只能找到访问查询表的唯一方法是整数索引.我想出了以下代码来进行概念的快速证明:
One inconvenience is accessing existing QueryTables within an Excel worksheet. Currently, the only method I can find to access a query table is by integer indexing. I came up with the following code for a quick proof of concept:
Sub RefreshDataQuery()
Dim querySheet As Worksheet
Dim interface As Worksheet
Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")
Dim sh As Worksheet
Dim QT As QueryTable
Dim startTime As Double
Dim endTime As Double
Set QT = querySheet.ListObjects.item(1).QueryTable
startTime = Timer
QT.Refresh
endTime = Timer - startTime
interface.Cells(1, 1).Value = "Elapsed time to run query"
interface.Cells(1, 2).Value = endTime
interface.Cells(1, 3).Value = "Seconds"
End Sub
这可行，但是我不想这样做.最终产品工具将具有多达五个不同的QueryTable.我想通过其名称引用QueryTable.
This works, but I don't want to do it this way. The end product tool will have up to five different QueryTables. I want to refer to a QueryTable by its name.
我该如何翻译:
Set QT = querySheet.ListObjects.item(1).QueryTable
类似于以下内容:
Set QT = querySheet.ListObjects.items.QueryTable("My Query Table")
推荐答案
根据，没有任何QueryTables集合作为ListObjects的属性.正确的代码是:
According to this MSDN link for ListObject there isn't any collection of QueryTables being a property of ListObjects. Correct code is:
Set QT = querySheet.ListObjects.items(1).QueryTable
您可能需要参考适当的ListObject item，例如(只是示例代码):
What you possibly need is to refer to appropriate ListObject item like (just example code):
Dim LS as ListObject
Set LS = querySheet.ListObjects("My LO 1")
Set QT = LS.QueryTable
另一种选择是以这种方式通过WorkSheet property引用QT:
The other alternative is to refer to QT through WorkSheet property in this way:
Set QT = Worksheet("QTable").QueryTables("My Query Table")
这篇关于通过名称引用QueryTable对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！