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.
|An array that contains parameters for the data
|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
|The connection data. If you are using a table, the
CommandText is Array(“YourTableName”); for a query,
CommandText is a SQL statement.
|True to keep the connection open while the workbook
remains open; False otherwise
|The command that creates the pivot table. Requires three
parameters: The TableDestination, TableName, and
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.
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 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.