通过名称引用QueryTable对象

问题描述：

我正在使用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")

答

根据

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对象

相关推荐