Crosstaby (tabele przestawne, cube'y) MS Excel - FAQ i rozwiązania problemów

2.2. Odświeżanie tabeli przestawnej Excel

Metody odświeżania tabeli przestawnych Microsoft Excel

Tabela przestawna Excel może być odświeżona na kilka sposobów:

Najłatwiejszym i najszybszym sposobem na odświeżenie tabeli przestawnej jest zaznaczenie obszaru crosstaba, prawy klik i wybór przycisku ‘Odśwież Dane’(‘Refresh Data’ w wersji angielskiej – ikona z czerwonym wykrzyknikiem obok). Jeżeli połączenie z bazą danych jest dostępne i w dalszym ciągu poprawnie skonfigurowane tabela przestawna zostanie odświeżona niezwłocznie.

Innym często używanym sposobem odświeżania danych jest użycie skryptu VBA. Dzięki skryptowi VBA uzyskujemy nieograniczone możliwości wyboru zdarzenia które wywołuje akcję odświeżenia. Może to być przykładowo zmiana wartości komórki, zadany przedział czasowy, zmiana tzw. focusu na formatce, naciśnięcie przycisku lub jakiekolwiek inne zdarzenie obsługiwane przez VBA Excel.
Aby odświeżyć tabelę przestawną należy użyć następującego polecenia VBA: [PivotTable object].Update.
Poniżej przykładowa procedura Visual Basic, która odświeża wszystkie crosstaby w pliku excelowym i loguje datę i czas ostatniego odświeżenia w komórce A1 w danym arkuszu.

Sub refresh_all_pivots()
‘deklaracja zmiennych lokalnych
Dim pt As PivotTable
Dim i As Integer

‘petla po arkuszach
For i = 1 To Sheets.Count
        For Each pt In Sheets(i).PivotTables
            pt.Update
            ‘loguj czas i date
            Sheets(i).Range("a1") = "Last updated: " & Date & " " & Time
        Next pt
Next i
End Sub

podobnym, ale nieznacznie bardziej zaawansowanym sposobem odświeżenia crosstaba MS Excel jest użycie Visual Basic Scripting (VBscript - Windows Host Script). VBSkrypt może być wywoływany z linii poleceń systemu operacyjnego i jest on interpretowany przez Microsoft Windows Script Host. Pliki ze skryptami VBSkrypt maja standardowo rozszerzenie .vbs
Poniżej pokazany został skrypt, który odświeża wszystkie crosstaby w arkuszu excelowym wskazanym w zmiennej workbookName.
Poniższy skrypt może być w łatwy sposób dostosowany do potrzeb. Przykładowo można rozszerzyć poniższy program o możliwość odświeżenia wszystkich plików xls z danego folderu na dysku. Należałoby stworzyć pętlę która będzie wykonywana dla każdego pliku xls z danego katalogu.

Dim excelObject as Object
Dim workbookName as String
Dim excelFileLocation as String
Dim nCntSheets as Integer

excelFileLocation = "d:\excel_pivot-tables\"
workbookName = "etl-tools_info-crosstab.xls"

set shell = createobject("wscript.shell")

' ******************** proces odswiezenia crosstaba Excel 
	Set excelObject = CreateObject("Excel.Application")

	'excelObject.Visible = true  ' opcja ukrywajaca okno Excel
	' otwarcie skoroszytu
	excelObject.workbooks.open excelFileLocation & workbookName 
	' wylaczenie ostrzezen.
	excelObject.Application.DisplayAlerts = False
	'wylacznie aktualizacji ekranu podczas odswiezania 
	excelObject.Application.ScreenUpdating = False

	'ilosc arkuszy w skoroszycie
	nCntSheets = excelObject.Workbooks(workbookName).sheets.count
	
	' funkcja odswiezajaca refresh_all_pivots, umieszczona w odswiezanym arkuszu w module refresh_funcs
	excelObject.Run "refresh_funcs.refresh_all_pivots()" 

	'****** zapisz i zamknij
	excelObject.Workbooks(workbookName).Save
	excelObject.Application.Quit

	'************* usun instancje Excel z pamieci
	SexcelObject = Nothing

' komunikat ukonczenia
MsgBox "Wszystkie tabele przestawne zostaly odswiezone"

?>