Excel crosstabs, pivot tables FAQ and best practices
- The easiest and fastest way to do it is to select a crosstab area, right click on it and select the ‘Refresh Data’ button (the one with a red exclamation mark icon). Once the connection is available and working it will refresh the pivot table straight away.
- Other commonly used way of refreshing the excel pivot table is from the VBA level. With that option the refresh script can be triggered on various types of events, as for example cell value change, in a given time interval, focus change, button click or any other event that can be caught from Excel VBA.
The VBA directive which allows the refresh of the pivot table is [PivotTable object].Update. Please find below a Visual Basic procedure to refresh all pivots in an Excel spreadsheet and log time and date of the last refresh.
Sub refresh_all_pivots() Dim pt As PivotTable Dim i As Integer For i = 1 To Sheets.Count For Each pt In Sheets(i).PivotTables pt.Update 'log date and time of last update Sheets(i).Range("a1") = "Last updated: " & Date & " " & Time Next pt Next i End Sub
- the most complex and advanced way of refreshing an excel crosstab is to use a Visual Basic Scripting (VBscript - Windows Host Script). The VB scripts can be executed from a command line and is processed by the operational system’s Microsoft Windows Based Script Host. The file extension is .vbs
In the example below there’s a script to refresh all pivot tables in a spreadsheet indicated by the workbookName string variable. It would be very easy to modify the script below so that it would refresh for example pivot tables in all excel files from a given folder. The only thing to modify in the script would be to loop through the file names in a directory and invoke the refresh script for each file.
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") ' ******************** Excel update process ********************** Set excelObject = CreateObject("Excel.Application") 'excelObject.Visible = true ' uncomment to have excel window open in foreground ' open the workbook excelObject.workbooks.open excelFileLocation & workbookName ' turn off alerts. It's necessary as pivot table gives message when updated excelObject.Application.DisplayAlerts = False 'do not follow changes on the screen excelObject.Application.ScreenUpdating = False 'count sheets nCntSheets = excelObject.Workbooks(workbookName).sheets.count ' the refresh function name is refresh_all_pivots and it exists in the refresh_funcs module in a spredsheet excelObject.Run "refresh_funcs.refresh_all_pivots()" '****** Save and close excelObject.Workbooks(workbookName).Save excelObject.Application.Quit '************* flush Excel instance from memory SexcelObject = Nothing ' message box MsgBox "All sheets updated successfully"
2.2. Excel crosstab refresh
Excel crosstab refresh methods
An excel crosstab can be refreshed in various ways: