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
                '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
    	'************* flush Excel instance from memory
    	SexcelObject = Nothing
    ' message box
    MsgBox "All sheets updated successfully"

  • Back to the list of all excel crosstabs FAQ topics