IT:PivotTable source data

From Stiki

PivotTables are useful ways to interrogate data within an Excel spreadsheet. They can be linked to a set of data within the spreadsheet, or even to an external data source such as an Access database. One slight issue with linking to an external data source is that the method for changing the data source (if required) is somewhat convoluted. For a spreadsheet that contains many PivotTables or needs to be updated regularly, it is preferable to automate this process.

(In case you're interested, the way to update the external data source is to open the PivotTable Wizard and step backwards through the Wizard until you can press the "Get Data" button. You can then change the SQL query, including the data source. It really isn't obvious though.)

The following piece of code rapidly updates the data source for PivotTables that are linked to an external data source such as a database. As written, this code assumes that only the filename needs to be updated, and that the structure of the new database, the appropriate SQL query, and the required fields for the PivotTables are all identical.

This may be useful for updating a spreadsheet to take account of the latest set of data for databases that are produced periodically, without having to overwrite the old database. A typical example would be to update the link for new monthly or quarterly data.

VBA Code

Public Sub Update_PivotTable_Links()
    ' Declare internal variables:
    Dim file_dialog As FileDialog, pivot As PivotTable ' Variables for referencing Excel objects
    Dim full_path As String, dir_only As String
    Dim char_pointer As Long
    Dim old_source As String, new_source As String
    Dim dbq_pos_before As Long, dbq_pos_after As Long
    Dim dir_pos_before As Long, dir_pos_after As Long
    Dim source_array() As String, array_pointer As Long
 
    ' Get the new full file path for the data source:
    Set file_dialog = Application.FileDialog(msoFileDialogFilePicker)
    With file_dialog
        .AllowMultiSelect = False
        If .Show = -1 Then
            full_path = .SelectedItems(1)
        Else
            full_path = ""
        End If
    End With
    Set file_dialog = Nothing ' Clear object from memory.
    
    If file_path <> "" Then
        ' Step backwards through the characters in the full path to find the last backslash
        ' and hence derive the split for the part of the file path that is the directory:
        char_pointer = Len(full_path)
        Do
            char_pointer = char_pointer - 1
        Loop Until Mid(full_path, char_pointer, 1) = "\"
        dir_only = Left(full_path, char_pointer - 1)
 
        ' Loop through each PivotTable on the worksheet that is linked to an external data source:
        For Each pivot In ActiveSheet.PivotTables
            If pivot.PivotCache.SourceType = xlExternal Then
                ' Get the current data source:
                old_source = pivot.SourceData(1)
                ' Derive the key character positions within this text string:
                dbq_pos_before = InStr(1, old_source, ";DBQ=") + 4
                dbq_pos_after = InStr(dbq_pos_before + 1, old_source, ";")
                dir_pos_before = InStr(1, old_source, ";DefaultDir=") + 11
                dir_pos_after = InStr(dir_pos_before + 1, old_source, ";")
 
                ' Construct a new data source string based on the new file path:
                new_source = Left(old_source, dbq_pos_before) _
                    & full_path & Mid(old_source, dbq_pos_after, dir_pos_before - dbq_pos_after + 1) _
                    & dir_only & Mid(old_source, dir_pos_after)
 
                ' Construct a new array of source data including the new file location:
                ReDim source_array(LBound(pivot.SourceData) To UBound(pivot.SourceData))
                For array_pointer = LBound(pivot.SourceData) To UBound(pivot.SourceData)
                    source_array(array_pointer) = pivot.SourceData(array_pointer)
                Next
                source_array(1) = new_source
 
                ' Update the PivotTable's source data and refresh the contents:
                pivot.SourceData = source_array
                pivot.RefreshTable
            End If
        Next
        Set pivot = Nothing ' Clear object from memory.
    End If
End Sub

Notes

The 'SourceData' property of a PivotTable object can take three different formats:

• If the data source is internal within Excel then the 'SourceData' is a string containing the appropriate cell reference.
• If the data source is internal, but split across multiple ranges then the 'SourceData' is a two-dimensional array of references and associated page fields.
• If the data source is external, the 'SourceData' is a one-dimensional array.

We are concerned with external data sources here, so we need to know what the format of this array is in more detail. It contains the following values:

• The location of the data source, for example: (I have split this across more than one line for clarity only; the actual field value is a single string with no line break.)

DSN=MS Access Database;DBQ=C:\Directory\Subdirectory\Filename.mdb;
DefaultDir=C:\Directory\Subdirectory;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

• The SQL query to extract the required data, for example:

SELECT PivotData.Field1, PivotData.Field2, PivotData.Field3, PivotData.Field4
FROM PivotData

The SQL query may be split across more than one field of the array. This is why in the above code we fill the 'source_array()' with the entire contents of 'pivot.SourceData', allowing for an array of any size (through use of LBound and UBound). The data source location is always in the first cell of the array, so we can then assign 'new_source' to 'source_array(1)'.

Note that in constructing the correct value of 'new_source' we take the current source data for the PivotTable (saved to 'old_source') and replace the current directory references. In order to do this we search the old source for the character positions of ";DBQ=" and the following ";" and for ";DefaultDir=" and the following ";". The semicolon character cannot appear within a valid file path, so this should accurately allow us to construct the replacement source.

Extensions

• This code assumes that all PivotTables on the worksheet all link to the same data source. So after getting the new data source, every PivotTable (that is linked to an external data source) is updated to point to the new source. If you wish to specify a different data source for each PivotTable then move the code that asks for the new data source (together with the code that conditions on a file having been selected and then derives the new file path for that source) within the loop across PivotTables:

    For Each pivot In ActiveSheet.PivotTables
        If pivot.PivotCache.SourceType = xlExternal Then
        ' Move data source selection code here.
        ' ...
        End If
    Next

• As it is currently written, the user is asked to select a new external data source by use of the file dialog object. This can be altered as required, perhaps to read in the new location from a named range on the worksheet.
• This code can be extended to work across multiple worksheets by the following modifications in the appropriate places:

    Dim sheet As Worksheet
    ' ...
    For Each sheet In ThisWorkbook.Worksheets
        ' ...
        For Each pivot In sheet.PivotTables ' Replacing 'ActiveSheet' with 'sheet'
            ' ...
        Next
    Next

A new external data source can be set once for the whole workbook, once per worksheet, or by other methods described above as required.