Excel VBA ссылается на объекты QueryTable по имени

Я разрабатываю инструмент MS Excel 2013 с VBA, который включает использование QueryTables. Одно неудобство, с которым я столкнулся, - это доступ к существующим QueryTables на листе Excel. В настоящее время единственным методом, который я могу найти для доступа к таблице запросов, является целая индексация. Я придумал следующий код для быстрого доказательства концепции

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

Это работает, но я действительно не хочу этого делать. Инструмент конечного продукта будет содержать до пяти различных QueryTables. Я хочу, чтобы ссылаться на QueryTable по его имени.

Что было бы неплохо, если бы я мог перевести код ниже

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

Что-то вдоль линий

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

Любые предложения будут высоко оценены.

Ответ 1

Согласно этой ссылке MSDN для ListObject, нет никакой коллекции 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

Другой альтернативой является обращение к QT через WorkSheet property следующим образом:

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

Ответ 2

В Excel 2003 и ранее внешнее соединение с данными создало объект QueryTable, родительский элемент которого был листом. Вы можете получить доступ к объекту QueryTable, для одного, через объект коллекции QueryTables. Как и большинство объектов коллекции, вы можете передать номер индекса или имя в метод Item (по умолчанию), чтобы получить его.

Sheet1.QueryTables("MyQtName")

Когда вы открываете рабочий лист 2003 в новой версии, он все еще имеет объект QueryTable и может быть доступен таким же образом. Даже если вы конвертируете формат файла, QueryTable сохраняется.

В 2007 и более поздних версиях существует только три способа создания QueryTable, который будет членом Worksheet.QueryTables:

Через код
Данные - из текста
Данные - из Интернета

Все другие внешние соединения данных UI в этих новых версиях приводят не к элементу QueryTables, а в ListObject. Этот ListObject будет иметь один и только один объект QueryTable, к которому можно получить доступ через свойство ListObject.QueryTable.

Вот плохая новость. QueryTable, чей родительский объект в ListObject не имеет свойства Name. Ну, это там, но вы получите ошибку времени выполнения 1004, если попытаетесь получить к ней доступ. Я предполагаю, что MS решила, что там только один QueryTable для ListObject, не имеет смысла, что оно должно иметь имя.

Если вы попытаетесь преобразовать Worksheet.QueryTables.QueryTable в ListObject, внешнее соединение данных исчезнет, а новый объект ListObject не будет иметь QueryTable.

Так как ваш QueryTables.Count возвращает ноль, все ваши QueryTables находятся внутри ListObjects и не имеют имен. У объектов ListObjects есть имена. Вы можете использовать

Sheet1.ListObjects("MyListName").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