Excel VBA按名称引用QueryTable对象 [英] Excel VBA refer to QueryTable objects by name

问题描述

我正在开发一个带有VBA的MS Excel 2013工具，涉及使用QueryTables。遇到的一个不便之处是在Excel工作表中访问现有的 QueryTables。目前，我可以找到访问查询表的唯一方法是整数索引。我想出了以下代码，以便快速验证概念。

  Sub RefreshDataQuery（）
 
 Dim querySheet作为工作表
 Dim接口作为工作表
 
设置querySheet =工作表（QTable）
设置接口=工作表（接口）
 
 Dim sh As Worksheet 
 Dim QT As QueryTable 
 
 Dim startTime As Double 
 Dim endTime As Double 
 
设置QT = querySheet.ListObjects.item（1） .QueryTable 
 
 startTime = Timer 
 QT.Refresh 
 endTime = Timer  -  startTime 
 
 interface.Cells（1，1）.Value =已过期时间运行查询
 interface.Cells（1，2）.Value = endTime 
 interface.Cells（1，3）.Value =Seconds
 
 End Sub 
  

这个工作，但我真的不想这样做。最终的产品工具最多可以有五个不同的QueryTables。我想要的是以名称引用QueryTable。

如果我可以将代码翻译成

$ b，那么会很好$ b

 设置QT = querySheet.ListObjects.item（1）.QueryTable 
  

对于某些东西

 设置QT = querySheet.ListObjects.items.QueryTable（我的查询表）
  

任何建议将不胜感激。

解决方案

根据 ListObject的MSDN链接没有任何 QueryTables 的集合是 ListObjects 。正确的代码是：

 设置QT = querySheet.ListObjects.items（1）.QueryTable 
  

您可能需要的是参考适当的 ListObject项目（只是示例代码） ：

  Dim LS as ListObject 
设置LS = querySheet.ListObjects（我的LO 1）
设置QT = LS.QueryTable 
  

另一个选择是通过 WorkSheet属性以这种方式：

 设置QT =工作表（QTable）。QueryTables（我的查询表）
  

I am developing a MS Excel 2013 tool with VBA which involves the use of QueryTables. One inconvenience I am experiencing 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

This works but I really don't want to do it this way. The end product tool will have up to five different QueryTables. What I want is to refer to a QueryTable by its name.

What would be nice is if I could translate the code below

Set QT = querySheet.ListObjects.item(1).QueryTable

To something along the lines

Set QT = querySheet.ListObjects.items.QueryTable("My Query Table")

Any suggestions would be much appreciated.

解决方案

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

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

The other alternative is to refer to QT through WorkSheet property in this way:

Set QT = Worksheet("QTable").QueryTables("My Query Table")

这篇关于Excel VBA按名称引用QueryTable对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！