I very often need to reformat OLAP-based pivot tables in Excel 2007 : change the layout to tabular , remove subtotals,
I want to automate these repetitive tasks with macros - I am a rather experienced vba programmer, yet
Doing this by using the pivot table object can lead to relatively complex code. A much simpler approach would be to use the
built-in commands that are available from the contextual ribbon pivottable tab. Understanding how to do this would also open
many doors for automating lots of other tasks.
I have searched and read numerous posts and articles including the
following MS article Executing a Built-In Command, downloaded the list of IDs files but I have so far not found a way to
make it work.
For each control, the list of IDs file specifies the following attributes:
Control Name,Control Type, Tab Set Name, Tab Name, Group Name,Parent Control, Secondary Parent Control, Tertiary Parent
Control, Ordering, Policy ID
The ribbon control that I want to execute is PivotTableLayoutShowInTabularForm which has a Policy ID = 12321
However, even though the current selected cell belongs to a pivot table, when I run the following vba line, I get the error
"Object variable or with block not set":
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I must be missing something. Can someone provide some help or code that shows how to "execute" a built-in button, even
if it is in a contextual ribbon tab ?