Excel crosstabs, pivot tables FAQ and best practices

2.2. Excel crosstab refresh

Excel crosstab refresh methods

An excel crosstab can be refreshed in various ways:

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"