logo标签列表

按名称引用 QueryTable 对象

excelvba
13

我正在使用VBA开发MS Excel 2013工具，其中涉及使用QueryTables。

一个不便之处是访问Excel工作表中 现有的 QueryTables。目前我只能通过整数索引来访问查询表。为了快速证明这一点，我想出了以下代码:

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。
Set QT = querySheet.ListObjects.item(1).QueryTable

翻译为：

将其改为以下内容：
Set QT = querySheet.ListObjects.items.QueryTable("My Query Table")
3个回答
10

在Excel 2003及以前版本中，外部数据连接将创建一个QueryTable对象，并将其父对象设置为工作表。您可以通过QueryTables集合对象访问QueryTable对象。像大多数集合对象一样，您可以通过传递索引号或名称给（默认的）Item方法来获取它。

Sheet1.QueryTables("MyQtName")
当您在新版本中打开2003工作表时，它仍然具有QueryTable对象，并且可以以相同的方式访问。即使您转换文件格式，QueryTable也会保留。
在2007年及以后的版本中，只有三种方法可以创建成为Worksheet.QueryTables成员的QueryTable：
1.通过代码 2.数据-来自文本 3.数据-来自Web
在这些新版本中，所有其他UI外部数据连接都不会生成QueryTables成员，而是生成ListObject。该ListObject将具有一个且仅有一个QueryTable对象，可以通过ListObject.QueryTable属性进行访问。
坏消息是，ListObject父级的QueryTable没有Name属性。它确实存在，但如果尝试访问它，则会收到运行时错误1004。我想微软认为既然每个ListObject只有一个QueryTable，那么它不应该有名称。
如果尝试将Worksheet.QueryTables.QueryTable转换为ListObject，则外部数据连接将消失，新ListObject将没有QueryTable。
由于QueryTables.Count返回零，所有的QueryTables都在ListObjects中，并且没有名称。ListObjects有名称。可以使用：
Sheet1.ListObjects("MyListName").QueryTable
这是一个函数，它接受一个名称和一个工作表，并返回一个QueryTable，该QueryTable具有该名称或者是具有该名称的ListObject的子项。
Public Function QueryTableByName(ByVal sName As String, ByRef sh As Worksheet) As QueryTable

    Dim qt As QueryTable
    Dim lo As ListObject

    On Error Resume Next
        Set qt = sh.QueryTables(sName)
    On Error GoTo 0

    If qt Is Nothing Then
        On Error Resume Next
            Set lo = sh.ListObjects(sName)
        On Error GoTo 0

        If Not lo Is Nothing Then
            On Error Resume Next
                Set qt = lo.QueryTable
            On Error GoTo 0
        End If
    End If

    Set QueryTableByName = qt

End Function
1
很棒的总结和函数！ - Doug Glancy
这非常有帮助和详细。谢谢！ - Paul Renton
1
ListObjects是一个集合对象，它保存工作表上所有ListObject对象。ListObject是一个保存单个实例的对象。集合对象从未具有Name或QueryTable属性，但ListObject仍然具有。至少根据MSDN。 - Dick Kusleika
@DickKusleika 我没有看到属于工作表的 ListObject 对象。它们可能已被释放。 - Paul Renton
@DickKusleika 好的，那很有道理。感谢您。但愿他们能保持一致，无论是通过编程还是通过UI创建的所有QueryTables都是工作表的子级。 - Paul Renton
显示剩余2条评论
6
根据 这个关于ListObject的MSDN链接，没有任何关于QueryTables的集合作为ListObjects属性的信息。正确的代码是：
Set QT = querySheet.ListObjects.items(1).QueryTable

您可能需要参考适当的 ListObject item，例如以下示例代码：
Dim LS as ListObject
Set LS = querySheet.ListObjects("My LO 1")
Set QT = LS.QueryTable

另一种选择是通过以下方式使用“WorkSheet属性”来引用QT： WorkSheet属性 
Set QT = Worksheet("QTable").QueryTables("My Query Table")
将QT设置为querySheet.ListObjects.items.QueryTable无法编译。我没有看到ListObjects的items属性。对于您的第二个建议，将QT设置为Worksheet（“QTable”）。QueryTables（“My Query Table”），这对我不起作用，因为QTable是预先存在的。 - Paul Renton
我将其更改为：Set QT = querySheet.ListObjects.items(1).QueryTable，这与您的代码类似。 - Kazimierz Jawor
你是说“工作表已经存在”是什么意思？这很明显——将QT的名称更改为您真正拥有的名称。您始终可以通过以下方式检查表格中QT的数量：Debug.Print Worksheets("QTable").QueryTables.Count，以及每个QT的名称：Debug.Print Worksheets("QTable").QueryTables(1).Name（第一个）。 - Kazimierz Jawor
1
抱歉如果之前没有表述清楚。QueryTable 不是通过 VBA 构建的，而是通过多个用户使用“数据连接向导”进行构建的。你之前尝试的代码计数将显示 0，即使我可以通过 ListObjects.item(1).QueryTable 访问 QueryTable。 - Paul Renton
1
所以，我猜ListObject数据来自外部源。因此，要进入QT，您需要找到适当的ListObject并使用ListObject.QueryTable引用。 - Kazimierz Jawor
1
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接