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.
