Classe PivotTable (Excel VBA)

A classe PivotTable representa um relatório de tabela dinâmica em uma planilha.

Os principais procedimentos da classe PivotTable são AddDataField, AddFields, PivotTables.Add e SlicerPivotTables.AddPivotTable

Set

Para usar uma variável de classe PivotTable, ela primeiro precisa ser instanciada por exemplo

Dim pvt as PivotTable
Set pvt = ActiveCell.PivotTable

Os procedimentos a seguir podem ser usados ​​para definir variáveis ​​do tipo PivotTable: PivotCache.SourceData, PivotCache.CreatePivotTable, PivotCell.Parent, PivotCell.PivotTable, PivotFields.Parent, PivotLayout.PivotTable, PivotTableChangeList.Parent, PivotTables.Item, PivotTables.Add, Range.PivotTable, SlicerPivotTables.Item, Worksheet.PivotTableWizard, Workbook.PivotTables, Worksheet.PivotTables e SlicerCache.PivotTables

For Each

Aqui está um exemplo de processamento dos itens PivotTable em uma coleção.

Dim pvt As PivotTable
For Each pvt In Application.PivotTables
	
Next pvt

Temas

Alguns procedimentos nesta classe foram agrupados em temas e são descritos em páginas de tema separadas

Add with its procedures AddDataField, AddFields, PivotTables.Add e SlicerPivotTables.AddPivotTable
Value with its procedures AllocationValue, RefreshDataSourceValues e Value
Column with its procedures ColumnFields, ColumnGrand, ColumnRange e PivotColumnAxis
Row with its procedures CompactRowIndent, PivotRowAxis, RowFields, RowGrand e RowRange
Formula with its procedures ConvertToFormulas, ListFormulas e PivotFormulas
Display/Show with its procedures DisplayContextTooltips, DisplayEmptyColumn, DisplayEmptyRow, DisplayErrorString, DisplayFieldCaptions, DisplayImmediateItems, DisplayMemberPropertyTooltips, DisplayNullString, ShowDrillIndicators e ShowValuesRow
Enable with its procedures EnableDataValueEditing, EnableDrilldown, EnableFieldDialog, EnableFieldList, EnableWizard e EnableWriteback
Sort/Order with its procedures FieldListSortAscending e SortUsingCustomLists
Name with its procedures GrandTotalName e RefreshName
Print with its procedures PageFieldOrder, PageFields, PageFieldStyle, PageFieldWrapCount, PageRange, PageRangeCells, PrintDrillIndicators, PrintTitles, RepeatItemsOnEachPrintedPage, ShowPageMultipleItemLabel, ShowPages e SubtotalHiddenPageItems

Métodos

Estes são os principais métodos da classe

AllocateChanges - Executa uma operação de write-back para todas as células editadas em um relatório da Tabela Dinâmica com base em uma fonte de dados OLAP.

CalculatedFields - Retorna uma coleção CalculatedFields que representa todos os campos calculados no relatório de tabela dinâmica especificado. Somente leitura.

ChangeConnection - Altera a conexão do objeto PivotTable especificado.

ChangePivotCache - Altera o objeto PivotCache da tabela dinâmicaespecificada.

ClearAllFilters - O método ClearAllFilters exclui todos os filtros atualmente aplicados a PivotTable. Isso inclui a exclusão de todos os filtros na coleção PivotFilters , a remoção de qualquer filtragem manual aplicada e a configuração de todos os campos PivotFields na área de filtro de relatório para o item padrão.

ClearTable - O método ClearTable é utilizado para limpar uma PivotTable. A limpeza de PivotTables inclui a remoção de todos os campos e a exclusão de todas as filtragens e classificação aplicadas a PivotTables. Este método redefine a PivotTable para o estado que ela tinha logo após ela ser criada, antes que quaisquer campos fossem adicionados a ela.

CommitChanges - Executa uma operação de confirmação na fonte de dados de um relatório de tabela dinâmica com base em uma fonte de dados OLAP.

CreateCubeFile - Cria um arquivo de cubo de um relatório de tabela dinâmica conectado a uma fonte de dados OLAP Online Analytical Processing.

DiscardChanges - Descarta todas as alterações nas células editadas de um relatório de Tabela Dinâmica com base em uma fonte de dados OLAP.

DrillDown - Permite detalhar os dados dentro de uma hierarquia de cubos baseada em OLAP ou no PowerPivot.

DrillTo - Permite analisar um local em uma hierarquia de cubos baseada em OLAP ou no PowerPivot.

DrillUp - Permite que você faça drill up nos dados de uma hierarquia de cubos baseada em OLAP ou no PowerPivot.

GetData - Retorna o valor dos dados arquivados em uma tabela dinâmica.

GetPivotData - Retorna um objeto Range com informações sobre um item de dados em um relatório de tabela dinâmica.

PivotCache - Retorna um objeto PivotCache que representa o cache para o relatório de tabela dinâmica especificado. Somente leitura.

PivotFields - Retorna um objeto que representa um único campo de tabela dinâmica um objeto PivotField ou uma coleção de ambos os campos Visible e Hidden (um objeto PivotFields ) no relatório de tabela dinâmica. Somente leitura.

PivotSelect - Seleciona parte de um relatório de tabela dinâmica.

PivotTableWizard - Cria e retorna um objeto PivotTable. Este método não exibe o assistente de tabela dinâmica. Este método não está disponível para fontes de dados OLE DB. Use o método Add para adicionar um cache de tabela dinâmica e, em seguida, crie um relatório de tabela dinâmica com base no cache.

PivotValueCell - Recupere o objeto PivotValueCell para uma determinada tabela dinâmica, desde determinados índices de linhas e colunas.

RefreshTable - Atualiza o relatório de tabela dinâmica a partir dos dados de origem. Retorna true se for bem-sucedido.

RepeatAllLabels - Especifica se rótulos de item devem ou não ser repetidos para todas as Tabelas Dinâmicas na Tabela Dinâmica especificada.

RowAxisLayout - Método usado para definir simultaneamente opções de layout para todos os PivotFields existentes.

SubtotalLocation - Esse método altera a localização do subtotal de todos os campo dinâmicos existentes. Alterar a localização do campo dinâmico tem um efeito visual imediato somente para campos em formato de estrutura de tópicos, mas ele também será definido para campos em formato tabular.

Update - Atualiza o relatório de tabela dinâmica selecionado.

PivotTables.Item - Retorna um único objeto de uma coleção.

SlicerPivotTables.RemovePivotTable - Remove uma referência feita a uma Tabela Dinâmica da coleção SlicerPivotTables.

Propriedades

ActiveFilters Indica o filtro atualmente ativo na PivotTable especificada. Somente leitura.

Dim pfsActiveFilters As PivotFilters
Set pfsActiveFilters = ActiveCell.PivotTable.ActiveFilters

Allocation Retorna ou define se é para executar uma instrução Update Cube para cada célula que é editada, ou somente quando o usuário escolhe calcular alterações ao executar uma análise de hipóteses em uma tabela dinâmica com base em uma fonte de dados OLAP. Leitura/gravação.

ActiveCell.PivotTable.Allocation = xlAutomaticAllocation

AllocationMethod Retorna ou define o método a ser usado para alocar valores ao executar a análise de hipóteses em um relatório de tabela dinâmica com base em uma fonte de dados OLAP. Leitura/gravação.

ActiveCell.PivotTable.AllocationMethod = xlEqualAllocation

AllocationWeightExpression Retorna ou define a expressão de peso MDX que será usada ao executar teste de hipóteses em uma Tabela Dinâmica com base em uma fonte de dados OLAP. Leitura/gravação.

ActiveCell.PivotTable.AllocationWeightExpression =

AllowMultipleFilters Define ou recupera um valor que indica se um PivotField pode ter vários filtros aplicados a ele ao mesmo tempo. Boolean de leitura/gravação.

ActiveCell.PivotTable.AllowMultipleFilters = True

AlternativeText Retorna ou define a cadeia de caracteres de texto descritivo alternativo para a Tabela Dinâmica. Leitura/gravação.

ActiveCell.PivotTable.AlternativeText =

CacheIndex Retorna ou define o número de índice do cache da tabela dinâmica. Long de leitura/gravação.

ActiveCell.PivotTable.CacheIndex =

CalculatedMembers Retorna uma coleção CalculatedMembers que representa todos os membros calculados e medidas calculadas para uma tabela dinâmica OLAP.

Dim cmsCalculatedMembers As CalculatedMembers
Set cmsCalculatedMembers = ActiveCell.PivotTable.CalculatedMembers

CalculatedMembersInFilters Retorna ou define se membros calculados de servidores OLAP devem ser avaliados em filtros. Leitura/gravação.

ActiveCell.PivotTable.CalculatedMembersInFilters = True

ChangeList Retorna a coleção PivotTableChangeList que representa a lista de alterações feitas na tabela dinâmica especificada com base em uma fonte de dados OLAP. Somente leitura.

Dim ptcChangeList As PivotTableChangeList
Set ptcChangeList = ActiveCell.PivotTable.ChangeList

CompactLayoutColumnHeader Especifica a legenda que será exibida no cabeçalho da coluna de uma PivotTable quando estiver no formato de layout de linha compacta. String de leitura/gravação.

ActiveCell.PivotTable.CompactLayoutColumnHeader =

CompactLayoutRowHeader Especifica a legenda que será exibida no cabeçalho da linha de uma PivotTable quando estiver no formato de layout de linha compacta. String de leitura/gravação.

ActiveCell.PivotTable.CompactLayoutRowHeader =

CubeFields Retorna a coleção CubeFields . Cada objeto cubefield contém as propriedades do elemento de campo de cubo. Somente leitura.

Dim cfsCubeFields As CubeFields
Set cfsCubeFields = ActiveCell.PivotTable.CubeFields

DataBodyRange Retorna um objeto Range que representa o intervalo de valores em uma tabela dinâmica. Somente leitura.

Dim rngDataBodyRange As Range
Set rngDataBodyRange = ActiveCell.PivotTable.DataBodyRange

DataFields Retorna um objeto que representa um único campo de tabela dinâmica um objeto PivotField ou uma coleção de todos os campos (um objeto PivotFields ) atualmente exibidos como campos de dados. Somente leitura.

Dim pfsDataFields As PivotFields
Set pfsDataFields = ActiveCell.PivotTable.DataFields

DataLabelRange Retorna um objeto Range que representa o intervalo que contém os rótulos dos campos de dados no relatório de tabela dinâmica. Somente leitura.

Dim rngDataLabelRange As Range
Set rngDataLabelRange = ActiveCell.PivotTable.DataLabelRange

DataPivotField Retorna um objeto PivotField que representa todos os campos de dados em uma tabela dinâmica. Somente leitura.

Dim pvtfDataPivotField As PivotField
Set pvtfDataPivotField = ActiveCell.PivotTable.DataPivotField

ErrorString Retorna ou define um valor String que representa a sequência de caracteres exibida nas células que contêm erros quando a propriedade DisplayErrorString é True.

ActiveCell.PivotTable.ErrorString =

HasAutoFormat True se o relatório de tabela dinâmica é formatado automaticamente quando é atualizado ou quando os campos são movidos. Boolean de leitura/gravação.

ActiveCell.PivotTable.HasAutoFormat = True

Hidden Verifica se a tabela dinâmica existe no nível da planilha. Boolean somente leitura.

Dim booHidden As Boolean
booHidden = ActiveCell.PivotTable.Hidden

HiddenFields Retorna um objeto que representa um único campo de tabela dinâmica um objeto PivotField ou uma coleção de todos os campos (um objeto PivotFields ) que não são exibidos atualmente como campos de linha, coluna, página ou dados. Somente leitura.

Dim pvtfHiddenFields As PivotField
Set pvtfHiddenFields = ActiveCell.PivotTable.HiddenFields

InGridDropZones Propriedade usada para ativar/desativar zonas para soltar na grade de um objeto PivotTable. Em alguns casos, ela também afeta o layout da tabela dinâmica. Boolean de leitura/gravação.

ActiveCell.PivotTable.InGridDropZones = True

InnerDetail Retorna ou define o nome do campo que será exibido como detalhe quando a propriedade ShowDetail for True para o campo de linha ou de coluna mais interno. String de leitura/gravação.

ActiveCell.PivotTable.InnerDetail =

LayoutRowDefault Esta propriedade especifica as configurações de layout de PivotFields quando adicionados à tabela dinâmica pela primeira vez. Leitura/gravação XlLayoutRowType.

ActiveCell.PivotTable.LayoutRowDefault = xlCompactRow

Location Obtém ou define uma cadeia de caracteres que representa a célula superior esquerda no corpo do objeto de tabela dinâmica especificado. Leitura/gravação.

ActiveCell.PivotTable.Location =

ManualUpdate True se o relatório de tabela dinâmica só for recalculado a pedido do usuário. O valor padrão é False. Boolean de leitura/gravação.

ActiveCell.PivotTable.ManualUpdate = True

MDX Retorna um String que indica o MDX Multidimensional Expression que seria enviado ao provedor para preencher o modo de tabela dinâmica. Somente leitura.

Dim strMDX As String
strMDX = ActiveCell.PivotTable.MDX

MergeLabels True se o item de linha externa do relatório de tabela dinâmica, o item de coluna, o SUBTOTAL e os rótulos gerais principais usam células mescladas. Boolean de leitura/gravação.

ActiveCell.PivotTable.MergeLabels = True

Name Retorna ou define um valor String que representa o nome do objeto.

ActiveCell.PivotTable.Name =

NullString Retorna ou define a cadeia de caracteres exibida nas células que contêm valores nulos quando a propriedade DisplayNullString é true. O valor padrão é uma cadeia de caracteres vazia "". String de leitura/gravação.

ActiveCell.PivotTable.NullString = "NA"

Parent Retorna o objeto pai do objeto especificado. Somente leitura.

Dim objParent As Object
Set objParent = ActiveCell.PivotTable.Parent

PivotChart Retorna um objeto Shape que representa o gráfico autônomo para o relatório de tabela dinâmica oculto especificado. Somente leitura.

Dim shpPivotChart As Shape
Set shpPivotChart = ActiveCell.PivotTable.PivotChart

PivotSelection Retorna ou define a seleção da tabela dinâmica no formato padão de seleção de relatório de tabela dinâmica. String de leitura/gravação.

ActiveCell.PivotTable.PivotSelection =

PivotSelectionStandard Retorna ou define um String que indica a seleção da tabela dinâmica em um formato de relatório de tabela dinâmica padrão usando as definições Inglês Estados Unidos. Leitura/gravação.

ActiveCell.PivotTable.PivotSelectionStandard = "1.57"

PreserveFormatting True se a formatação é preservada quando o relatório é atualizado ou recalculado por operações como dinamização, classificação ou alteração de itens de campo de página.

ActiveCell.PivotTable.PreserveFormatting = True

RefreshDate Retorna a data em que o relatório de tabela dinâmica foi atualizado pela última vez. Date somente leitura.

Dim dtRefreshDate As Date
dtRefreshDate = ActiveCell.PivotTable.RefreshDate

SaveData True se os dados do relatório de tabela dinâmica forem salvos com a pasta de trabalho. False se somente a definição de relatório for salva. Boolean de leitura/gravação.

ActiveCell.PivotTable.SaveData = True

SelectionMode Retorna ou define o modo de seleção estruturada do relatório de tabela dinâmica. Leitura/gravação XlPTSelectionMode.

ActiveCell.PivotTable.SelectionMode = xlBlanks

ShowTableStyleColumnHeaders A propriedade ShowTableStyleColumnHeaders é definida como true se os cabeçalhos de coluna devem ser exibidos na tabela dinâmica. Boolean de leitura/gravação.

ActiveCell.PivotTable.ShowTableStyleColumnHeaders = True

ShowTableStyleColumnStripes A propriedade ShowTableStyleColumnStripes exibe colunas em tiras nas quais colunas pares são formatas de forma diferente das colunas ímpares. Isso facilita a leitura as tabelas dinâmicas. Boolean de leitura/gravação.

ActiveCell.PivotTable.ShowTableStyleColumnStripes = True

ShowTableStyleLastColumn Retorna ou define se a última coluna é exibida para o objeto PivotTable especificado. Boolean de leitura/gravação.

ActiveCell.PivotTable.ShowTableStyleLastColumn = True

ShowTableStyleRowHeaders A propriedade ShowTableStyleRowHeaders é definida como true se os cabeçalhos de linha devem ser exibidos na tabela dinâmica. Boolean de leitura/gravação.

ActiveCell.PivotTable.ShowTableStyleRowHeaders = True

ShowTableStyleRowStripes A propriedade ShowTableStyleRowStripes exibe linhas em tiras nas quais linhas pares são formatas de forma diferente das linhas ímpares. Isso facilita a leitura as tabelas dinâmicas. Boolean de leitura/gravação.

ActiveCell.PivotTable.ShowTableStyleRowStripes = True

Slicers Retorna a coleção slicers para a tabela dinâmica especificada. Somente leitura.

Dim slcsSlicers As Slicers
Set slcsSlicers = ActiveCell.PivotTable.Slicers

SmallGrid True se o Microsoft Excel usa uma grade que tenha duas células de largura e duas células de profundidade para um relatório de tabela dinâmica criado recentemente. False se o Excel usar um contorno estêncil em branco. Boolean de leitura/gravação.

ActiveCell.PivotTable.SmallGrid = True

SourceData Retorna a fonte de dados para um relatório de tabela dinâmica, como mostra a tabela a seguir. de leitura/gravação.

ActiveCell.PivotTable.SourceData =

Summary Retorna ou define a descrição associada à cadeia de caracteres alternativa da Tabela Dinâmica especificada. Leitura/gravação.

ActiveCell.PivotTable.Summary =

TableRange1 Retorna um objeto Range que representa o intervalo que contém o relatório de tabela dinâmica inteiro, mas não inclui campos de página. Somente leitura.

Dim rngTableRange1 As Range
Set rngTableRange1 = ActiveCell.PivotTable.TableRange1

TableRange2 Retorna um objeto Range que representa o intervalo que contém o relatório de tabela dinâmica inteiro, incluindo campos de página. Somente leitura.

Dim rngTableRange2 As Range
Set rngTableRange2 = ActiveCell.PivotTable.TableRange2

TableStyle2 A propriedade TableStyle2 especifica o estilo aplicado no momento à tabela dinâmica. Leitura/gravação.

ActiveCell.PivotTable.TableStyle2 = "PivotStyleLight17"

Tag Retorna ou define uma sequência de caracteres salva com o relatório de tabela dinâmica. String de leitura/gravação.

ActiveCell.PivotTable.Tag = "Product Sales by Region"

TotalsAnnotation True se um asterisco * é exibido ao lado de cada valor de subtotal e total geral no relatório de tabela dinâmica especificado se o relatório for baseado em uma fonte de dados OLAP. O valor padrão é True. Boolean de leitura/gravação.

ActiveCell.PivotTable.TotalsAnnotation = True

VacatedStyle Retorna ou define o estilo aplicado às células tornadas vagas quando o relatório de tabela dinâmica é atualizado. O valor padrão é uma sequência de caracteres nula nenhum estilo é aplicado por padrão. String de leitura/gravação.

ActiveCell.PivotTable.VacatedStyle = "BlackAndBlue"

Version Retorna um valor XlPivotTableVersionList que representa o número da versão do Microsoft Excel.

Dim xptVersion As XlPivotTableVersionList
xptVersion = ActiveCell.PivotTable.Version

ViewCalculatedMembers Quando definida como True padrão, os membros calculados de tabelas dinâmicas OLAP (Online Analytical Processing) podem ser visualizados. Boolean de leitura/gravação.

ActiveCell.PivotTable.ViewCalculatedMembers = True

VisibleFields Retorna um objeto que representa um único campo em um relatório de tabela dinâmica um objeto PivotField ou uma coleção de todos os campos visíveis (um objeto PivotFields ). Os campos visíveis são exibidos como campos de linha, coluna, página ou dados. Somente leitura.

Dim pfsVisibleFields As PivotFields
Set pfsVisibleFields = ActiveCell.PivotTable.VisibleFields

VisualTotals True padrão para permitir que as tabelas dinâmicas OLAP (Online Analytical Processing) calculem novamente um total depois que um item foi ocultado. Boolean de leitura/gravação.

ActiveCell.PivotTable.VisualTotals = True

VisualTotalsForSets Retorna ou define se os itens filtrados devem ser incluídos nos totais de conjuntos nomeados para a tabela dinâmica especificada. Leitura/gravação.

ActiveCell.PivotTable.VisualTotalsForSets = True

PivotTables.Count Retorna um valor Long que representa o número de objetos na coleção.

Dim lngCount As Long
lngCount = ActiveSheet.PivotTables.Count

PivotTables.Parent Retorna o objeto pai do objeto especificado. Somente leitura.

Dim objParent As Object
Set objParent = ActiveSheet.PivotTables.Parent

SlicerPivotTables.Count Retorna o número de objetos na coleção.

Dim lngCount As Long
lngCount = ActiveWorkbook.SlicerCaches(1).PivotTables.Count

SlicerPivotTables.Item Retorna um único objeto PivotTable da coleção.

Dim pvtItem As PivotTable
Set pvtItem = ActiveWorkbook.SlicerCaches(1).PivotTables(Index:=1)

SlicerPivotTables.Parent Retorna o objeto SlicerCache que é o pai da coleção SlicerPivotTables especificada. Somente leitura.

Dim objParent As Object
Set objParent = ActiveWorkbook.SlicerCaches(1).PivotTables.Parent