枚举Excel WorkSHEET中的WorkSHEET对象

Question

虽然我知道如何(在VBA代码中)枚举整个工作簿中的WorkbookQuery对象，但我需要知道如何对一个工作表进行枚举。这是适用于整个工作簿的代码..。

Option Explicit

Public Sub EnumerateWorkbookQueries()

    Dim q As WorkbookQuery

    For Each q In ThisWorkbook.Queries
        Debug.Print q.Name
    Next q

End Sub

我知道WorkbookQuery对象(顾名思义)是在工作簿中收集的，但是它们返回的数据会进入特定工作表上的表中。当您单击其中一个表时，Excel知道如何在查询列表中突出显示其关联的WorkbookQuery对象(假设用户已打开该对象)。

Answer 1

对于显示在工作表上的每个查询，都有一个自动创建的工作簿作用域名称，该名称引用一个ListObject，该名称显示查询结果(在名称管理器中查看它们)。这些ListObject具有一个WorkbookConnection属性，其名称与Query名称相关。

如果您有一个名为“如MyQuery”的查询(显示在工作表上)，则会有一个WorkbookConnection名为Query - MyQuery的ListObject。

如果您查看Excel中的“现有连接”对话框，连接将使用"Query -“前缀列出，但如果您编辑连接，则名称属性不带该前缀即可编辑。在进行名称更改时，前缀将自动重新添加。编辑查询的名称将更新WorkbookConnection名称，而visa则会更新查询名称。

您可以将此设置为检查任何给定工作表上的查询。

Public Sub EnumerateWorksheetQueries(ws As Worksheet)
    Dim lo As ListObject
    Dim qt As QueryTable

    If ws.ListObjects.Count > 0 Then
        For Each lo In ws.ListObjects
            Set qt = Nothing
            On Error Resume Next
                Set qt = lo.QueryTable
            On Error GoTo 0
            If Not qt Is Nothing Then
                Debug.Print  ws.Parent.Queries(Mid$(lo.QueryTable.WorkbookConnection.Name, 9)).Name
            End If
        Next
    End If
End Sub

就像这样吧

Sub Demo1
    EnumerateWorksheetQueries ActiveSheet
End Sub

或

Sub Demo2()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print "Queries on " & ws.Name
        EnumerateWorksheetQueries ws
    Next
End Sub

注意:其中一个问题是，ListObject的默认名称也与查询相关，但用户可以更改。更改ListObject名称时，查询和WorkbookConnection的名称不会更新。