Tag Archives: macros

Excel, Programming

How to select slicer values with VBA

PitanHeader_short

Hello, everyone, and welcome to another edition of Pitan Pivot Mage!

Today we will cover how to select slicer values with VBA. This is especially useful if you have a report generated by OfficeWriter’s ExcelTemplate model and you can’t select the slicer values in the template file.

Intro

Let’s start out with an ExcelWriter report template. It has some data off to the side and a PivotTable that summarizes the test scores with averages. There are two slicers for filtering on age and gender.

Template

When it’s populated with data, all of the values in the slicers are automatically selected, so the default is a full view of the data.

RenderedReport

Writing the Macros

Whenever I need to write a PivotTable macro, I always start by recording myself performing the action I want and then generalize the code later. In this case, I want to select only “M” and “17” in my slicers for 17-year-old male students.

My first pass gives me some useful information:

RawMacro

The first thing I notice is the slicer name “Slicer_Age1” and “Slicer_Gender1”. This is the name for the slicer for formulas and VBA. To find the name of your slicer, select the slicer and go to slicer settings. This is on the left, under slicer tools:

SlicerTools_Settings

The slicer tool dialog lists the name of the slicer, but also the Name to use in formulas. This is the programmatic name for the slicer that we’ll use in the macro.

Slicer_Programmatic_Age

Then I was able to generalize the macro to take a slicer name, value, and whether it should be selected or deselected:


'Selects or deselects a value (slicerVal) in a slicer (slicerName)
Sub SelectSlicerValue(ByVal slicerName As String,
ByVal slicerVal As String, ByVal isSelected As Boolean)
ActiveWorkbook.SlicerCaches(slicerName).SlicerItems(slicerVal).Selected = isSelected
End Sub

To make things easier for myself, I  created a subroutine that would select 17 and “M,” while deselecting 18 and “F.”

'Selects slicer values to display only data for 17-year-old male students
Sub SelectMale17Profile()
SelectSlicerValue "Slicer_Age1", "17", True
SelectSlicerValue "Slicer_Age1", "18", False
SelectSlicerValue "Slicer_Gender1", "M", True
SelectSlicerValue "Slicer_Gender1", "F", False
End Sub

Calling the Macros

To make sure that the macro runs when the workbook is opened, I called the subroutine in the Workbook_Open() code.


Private Sub Workbook_Open()
SelectMale17Profile
End Sub

Handling the PivotTable refresh

As outlined in our tutorial for how to use PivotTables with ExcelTemplate reports, the PivotTable needs to refresh in order for the imported data to display; the easiest way to do this is to set ‘Refresh on Open’ under PivotTable Data Properties. This poses a problem because macros in Workbook_Open() execute before the the PivotTable refresh and the slicer macros will be unable to find the values to select. This results in a run-time error.

To avoid this problem, I wrote a quick macro that refreshes the PivotTable:

'Refreshes the PivotTable as a replacement for the 'refresh on open' property
Sub PivotTableRefresh(ByVal pivotTableSheet As String, ByVal pivotTableName As String)
ActiveWorkbook.Sheets(pivotTableSheet).PivotTables(pivotTableName).PivotCache.Refresh
End Sub

And added it before the slicer code in Workbook_Open().

Private Sub Workbook_Open()
'Refresh the PivotTable with a macro because it may not refresh first
PivotTableRefresh "Template", "PivotTable1"
'Select the desired slicer values
SelectMale17Profile
End Sub

There you go!

SelectedProfile

Downloads

A copy of the template file with the macros, a sample output file, and the code used to populate the template file are available for download here as a zip file.

Getting started:

Learn more about ExcelTemplate or try it out in a free trial of OfficeWriter today.

     OR     



Excel

Run-time error 1004 with PivotItems

I was working on some VBA code to hide all the values for a particular report filter, except for a specific one. The code looped through all the pivot items for a particular field, turned them off and then turned the one I wanted back on.

Sub SetFilter(ByVal Worksheet As String, ByVal pivotTable As String, ByVal pivotField As String, ByVal selectValue As String) 
      Sheets(Worksheet).Select 

      Dim pvtTable As pivotTable
      Set pvtTable = ActiveSheet.PivotTables(pivotTable) 

      Dim pvtField As pivotField 
      Set pvtField = pvtTable.PivotFields(pivotField) 
     For Each filterValue In pvtField.PivotItems 
          filterValue.Visible = False 
     Next 
     pvtField.PivotItems(selectValue).Visible = True 
End Sub

While testing the code, it worked until it hit the last filter value, at which point it would throw an exception: Continue reading Run-time error 1004 with PivotItems →

Excel, OfficeWriter

Pitan Pivot Mage and the Quest to Slay the Bracket Problem

All is not quiet on the home front…

“Cannot open PivotTable source file” – If you have ever encountered this error message, then you know the frustrations of The Bracket Problem. If you haven’t run into this issue before, let me paint you a picture:

There some sort of pivot report on a server, for example, a report that’s being dynamically generated for Excel using OfficeWriter. An end user decides to download the report on her work machine with IE 7. When she opens the workbook, instead of the pivot table refreshing, she gets a warning message:

Cannot open PivotTable source file: ‘C:\Users\Pitan\AppData\Local\Microsoft\Windows\Temporary Internet Files\….\ [PitanPivotReport[1].xlsx]Sheet1’.

When she tries downloading the report using Mozilla FireFox, the warning message doesn’t occur.

This, my friends, is The Bracket Problem.

What trickery is afoot? Continue reading Pitan Pivot Mage and the Quest to Slay the Bracket Problem →