如何获取数据透视表的ListObject对象？ [英] How can I get the ListObject object of a PivotTable?

问题描述

这个问题最初是由Hugh Seagraves发布的一个有关问题的答案的一部分。他想在一个工作表上引用一个列表对象（一个表），这是一个不同工作表上的数据透视表，并且确实有一个相当不错的答案，但是询问是否有更直接的方式。我试图解决他的答案，但其他人则认为改变了他的答案的目的。

This question was originally posted as part of an answer by Hugh Seagraves on a related question. He "wanted to refer to a list object (a table) on one worksheet that a pivot table on a different worksheet refers to," and did have a fairly good answer, but asked if there was a more direct way. I tried to tack onto his answer, but others felt it was changing the purpose of his answer.

他的推理是：由于列表对象是Worksheets集合的一部分，您必须知道列表对象所在的工作表的名称才能引用它。他提供了一些代码，如果您尚未创建数据透视表，它将输出表（ListObject）的名称。但是，鉴于数据透视表名称，有一些通用代码可能有助于获取任何创建的数据透视表的 ListObject 对象。我正在回答我自己（实际上是休）的问题，但随时随地都可以改进！

His reasoning was, "Since list objects are part of the Worksheets collection, you have to know the name of the worksheet that list object is on in order to refer to it." He provided some code, which would output the name of the table (ListObject) if you hadn't already created the Pivot Table. But I thought it might be helpful to have some generic code to get the ListObject object of any created Pivot Table, given the PivotTable name. I'm answering my own (actually Hugh's) question, but feel free to chime in with any improvements!

推荐答案

数据透视表它是数据透视数据库的数据。因此，您只需使用PivotCache.SourceData属性来查询ListObject名称。

A PivotTable gets it's data from it's PivotCache. So you just need to use the PivotCache.SourceData property to query the ListObject name.

例如，如果我组成一个基于ListObject的数据透视表，那么如果我选择数据透视表中的一个单元格可以使用：

For instance, if I make up a PivotTable based on a ListObject, then if I select a cell in the PivotTable then I can use this:

? activecell.PivotTable.PivotCache.SourceData
Table1

这篇关于如何获取数据透视表的ListObject对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！