Wyświetlanie informacji o tabelach przestawnych w Excel VBA

Często podczas tworzenia dużych raportów w Excel chcielibyśmy zweryfikować, czy wszystkie elementy zawarte w raporcie są nam potrzebne. Szczególnym przypadkiem takich obiektów są tabele przestawne. Tabele przestawne jak wiemy, nie tylko prezentują dane, ale także je przechowują. Jeśli chcielibyśmy uzyskać więcej informacji o tabelach w naszym raporcie możemy je uzyskać przy pomocy kodu VBA. Skupmy się na elementach kluczowych takich jak:

• Nazwa tabeli przestawnej interpretowana przez Excela. To własnie za pomocą tej nazwy możemy odnieść się w kodzie vba do naszej tabeli.
• Arkusz i zakres komórek w których przechowywana jest tabela.
• Arkusz i zakres komórek, w których przechowywane jest źródło danych użytych w tabeli.
• Data ostatniego odświeżenia tabeli.
• Nazwa użytkownika , a dokładnie login domenowy, który jako ostatni odświeżył tabelę
• Styl tabeli przestawnej, by sprawdzić czy wszystkie tabele w raporcie mają taki sam styl.

To, co musimy zrobić, to napisać program z użyciem pętli For…Each..Next, przechodzący przez wszystkie arkusze i wszystkie tabele przestawne w naszym Excelu. Program po kolei będzie zbierał potrzebne nam dane z tabel przestawnych. Przykład takiego makra zamieszczam poniżej:

Sub ListPivotsInfo()
 Dim objWks As Worksheet
 Dim objNewWks As Worksheet
 Dim objPivot As PivotTable
 Dim intRow As Integer
 Set objNewWks = Worksheets.Add
 intRow = 1
 With objNewWks
 .Name = "PIVOT_INFO_" & "_" & DatePart("h", Now()) & DatePart("n", Now()) & DatePart("s", Now())
 .Cells(intRow, 1) = "PIVOT_NAME"
 .Cells(intRow, 2) = "DATA_SOURCE"
 .Cells(intRow, 3) = "REFRESHED_BY"
 .Cells(intRow, 4) = "LAST_REFRESH"
 .Cells(intRow, 5) = "PIVOT_RANGE"
 .Cells(intRow, 6) = "PIVOT_SHEET"
 .Cells(intRow, 7) = "PIVOT_STYLE"
 For Each objWks In ActiveWorkbook.Worksheets
 For Each objPivot In objWks.PivotTables
 intRow = intRow + 1
 .Cells(intRow, 1).Value = objPivot.Name
 .Cells(intRow, 2).Value = objPivot.SourceData
 .Cells(intRow, 3).Value = objPivot.RefreshName
 .Cells(intRow, 4).Value = objPivot.RefreshDate
 .Cells(intRow, 5).Value = objPivot.TableRange1.Address
 .Cells(intRow, 6).Value = objWks.Name
 .Cells(intRow, 7).Value = objPivot.TableStyle2
 
 Next
 Next
 .Select
 End With
End Sub

Jak widzimy makro tworzy nowy arkusz w Excelu, w którym wszelkie informacje zostaną wprowadzone do odpowiednich kolumn tabeli. Arkusz w swojej nazwie posiada datę i godzinę wygenerowania, by móc odróżnić poszczególne wersje, które będziemy generować.