Как я могу получить объект ListObject сводной таблицы?

Question

Этот вопрос был первоначально размещен как часть ответа Хью Сигрейвса на связанный вопрос. Он "хотел сослаться на объект списка (таблицу) на одном рабочем листе, на который ссылается сводная таблица на другом рабочем листе", и получил довольно хороший ответ, но спросил, существует ли более прямой путь. Я пытался уловить его ответ, но другие чувствовали, что это меняет цель его ответа.

Он рассуждал так: "Поскольку объекты списка являются частью коллекции" Рабочие листы ", вы должны знать имя рабочего листа, на котором находится объект списка, чтобы ссылаться на него". Он предоставил некоторый код, который выводит имя таблицы (ListObject), если вы еще не создали сводную таблицу. Но я подумал, что было бы полезно иметь некоторый общий код для получения объекта ListObject любой созданной сводной таблицы с заданным именем сводной таблицы.

Answer 1

Сводная таблица получает данные из своей сводной кэш-памяти. Поэтому вам просто нужно использовать свойство PivotCache. SourceData для запроса имени ListObject.

Например, если я создаю сводную таблицу на основе объекта ListObject, то, если я выберу ячейку в сводной таблице, я смогу использовать это:

? activecell.PivotTable.PivotCache.SourceData
Table1

Учитывая, что имена таблиц уникальны в книге, а также являются именованными диапазонами, то для установки ссылки на фактический объект ListObject вы просто используете что-то вроде этого:

Set lo = Range(ActiveSheet.PivotTables("SomePivotTable").PivotCache.S‌​ourceData).ListObjec‌​t

Зная это, мы можем написать функцию, которая принимает объект PivotTable и возвращает объект ListObject, который содержит источник данных PivotTable, вот так:

Public Function GetListObjectForPT(pt As PivotTable) As ListObject
On Error Resume Next ' In case the Pivot isn't created from a ListObject
Set GetListObjectForPT = Range(pt.PivotCache.SourceData).ListObject
End Function

... и вы можете использовать это так:

Sub Macro1()
Dim pt As PivotTable
Dim lo As ListObject

Set pt = Worksheets("SomeWorksheetName").PivotTables("SomePivotTableName")
Set lo = GetListObjectForPT(pt)

End Sub