Changing an external data source for a pivot table

A while ago I was asked to build a reporting tool that consisted of twenty or so pivot tables based on an external data source. The data in the original query was filtered by State, so that users could only see data that related to them. Once completed, the tool was made available to the other sections of the business, which meant changing the filters. Doing this by hand was tedious: each pivot table needed the State to be reset and, when the table was refreshed, the drop-down lists contained unwanted items from the previous States. It was time to go for a dig and see whether these properties could be manipulated in VBA.

Changing the filter on the query for the external data source

By recording the process of creating a pivot table from an external source, it is possible to see what parameters are used for the PivotCache property.

Property	Description
Connection	An array that contains parameters for the data connection
CommandType	Defines the type of data that the CommandText contains. If your data source is a query, CommandType will be xlCmdSql; if you are using a table it is xlCmdTable; for an OLAP cube it is xlCmdCube, and for a SharePoint list it is xlCmdList
CommandText	The connection data. If you are using a table, the CommandText is Array("YourTableName"); for a query, CommandText is a SQL statement.
MaintainConnection	True to keep the connection open while the workbook remains open; False otherwise
CreatePivotTable	The command that creates the pivot table. Requires three parameters: The TableDestination, TableName, and DefaultVersion.

At least in Excel 2007, you can use either xlCmdTable or xlCmdSql with Array("YourTableName") or a SQL statement. All four permutations worked when I tested them.

The parameter that defines the SQL for the data source is CommandText, so to modify the filters this is the parameter to use.

Removing the old names from drop-downs

When you change a pivot table to reference a new data set, you will find that the names from the old data set still appear in the drop-down options of row and column fields. This "pivot junk" is annoying for users, and although it can be removed manually the process is tedious. It's much simpler to use a line of code to do the job.

The code below does both jobs: resets the query for the pivot cache, and removes any unwanted items from the drop-downs in the pivot table. The original pivot cache retrieved all records from the UK_CarSales table, but the new SQL excludes manufacturers with names starting from A to L.

Sub ChangeSource()
  Dim sSQL As String
  Dim PT As PivotTable

  'define the new data query
  sSQL = "SELECT * FROM UK_CarSales WHERE Manufacturer>'M'"

  For Each PT In ActiveSheet.PivotTables
    'change the source, remove missing items, and requery the PT
    PT.PivotCache.CommandText = sSQL
    PT.PivotCache.MissingItemsLimit = xlMissingItemsNone
    PT.PivotCache.Refresh
  Next PT
End Sub

This sample will reset all pivot tables on the active sheet. It's straightforward to adapt it so that you loop through all worksheets in the workbook.

  Home | About | Links | Contact | Excel | Access