Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottable Pivotselect string
I have a pivottable which I am attempting to format through VBA.
I use a table driven approach to loop through the fields in the pivot table to format various things (borders, numberformat, bold, etc). I want to be able to use the pivotslect method to be able to format the fields, but I'm having little joy. Specifically, I have no problem selecting the datafields and their labels, but it's the rowfield totals which are killing me. Using the macrorecorder I have the following.. ActiveSheet.PivotTables("Tableau croisé dynamique5").PivotSelect _ "'Centre de Cout'[Tous;Somme]", xlDataAndLabel I'm afraid it's in French (I'm using French excel). The problem with the above is that it does not work when I try to apply it in a procedure/ immediate window. Anyone have any thoughts? I've been looking through the "standard pivottable report selection" documentation and it tells me about spaces and use of single quotation marks, but truth be told I was less clear after having read it. In a perfect world I would like to have a function which converts the field name and the mode into "standard pivottable report selection format". Anyone have something that can help me out. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottable Pivotselect string
Hi,
You might consider something like this: ActiveSheet.PivotTables("PivotTable2").Format xlReport6 xlReport6 is a constant associated with a table format (Auto Format). Examples of the formats can be seen by righ clicking anywhere in the Pivot table and looking at format report. Using these special formats allows you to flip the pivot table around without loosing the formating you've done. So, you can pick any color scheme and formatting you like and set the format with code then use a bit more code to layout the pivot table as you like Mike -----Original Message----- I have a pivottable which I am attempting to format through VBA. I use a table driven approach to loop through the fields in the pivot table to format various things (borders, numberformat, bold, etc). I want to be able to use the pivotslect method to be able to format the fields, but I'm having little joy. Specifically, I have no problem selecting the datafields and their labels, but it's the rowfield totals which are killing me. Using the macrorecorder I have the following.. ActiveSheet.PivotTables("Tableau croisé dynamique5").PivotSelect _ "'Centre de Cout'[Tous;Somme]", xlDataAndLabel I'm afraid it's in French (I'm using French excel). The problem with the above is that it does not work when I try to apply it in a procedure/ immediate window. Anyone have any thoughts? I've been looking through the "standard pivottable report selection" documentation and it tells me about spaces and use of single quotation marks, but truth be told I was less clear after having read it. In a perfect world I would like to have a function which converts the field name and the mode into "standard pivottable report selection format". Anyone have something that can help me out. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottable Pivotselect string
Mike
That's excellent. I'm really kicking myself that I've been wastng so much time on formatting when there exists this property which will do 90% of the formatting that I require... and I'll never have to worry about trying to get those standard pivot strings again. Thanks again Mohsen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a PivotTable w/o selecting data in an existing PivotTable | Excel Discussion (Misc queries) | |||
How does one get the PivotTable report info that feeds another PivotTable report? | Excel Programming | |||
PivotTable - PivotTable Field name is not valid - error! | Excel Programming | |||
PivotTable and connection string | Excel Programming | |||
PivotTable and connection string | Excel Programming |