Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 Pivotchart Macro
Here's the line:
..Pivotfields(ls_measures(ln_measure, 0)).Orientation = xlHidden With block is set to ActiveChart.PivotLayout.PivotTable. ls_measures is a string array (0 to 5, 0 to 3). (x, 0) contains the caption; (x, 1) contains the data field function; (x, 2) contains the pivottable field name; (x, 3) contains the number format. It fails on ls_measures(5, 0), a caclulated field (run time error '1004'; unable to set the orientation property of the pivotfield class). I'm current on SPs and updates. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 Pivotchart Macro
I found the answer to my own question. I replaced it with the following:
If .PivotFields(ls_measures(ln_measure, 2)).IsCalculated Then .PivotFields(ls_measures(ln_measure, 2)).Delete .CalculatedFields.Add ls_measures(ln_measure, 2), ls_measures(ln_measure, 4), True Else .PivotFields(ls_measures(ln_measure, 0)).Orientation = xlHidden End If If you have a data field based on a calculated field, you can check the "IsCalculated" Property. If it's true, I delete the base calculation, which removes the data field (without errors, I might add). I then created another array element that has the calculation. Based on that information, I recreate the calculation field. This spreadsheet is now *totally* automated. "Sam Tyler" wrote: Here's the line: .Pivotfields(ls_measures(ln_measure, 0)).Orientation = xlHidden With block is set to ActiveChart.PivotLayout.PivotTable. ls_measures is a string array (0 to 5, 0 to 3). (x, 0) contains the caption; (x, 1) contains the data field function; (x, 2) contains the pivottable field name; (x, 3) contains the number format. It fails on ls_measures(5, 0), a caclulated field (run time error '1004'; unable to set the orientation property of the pivotfield class). I'm current on SPs and updates. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to create pivotchart problems | Excel Discussion (Misc queries) | |||
Duplicating PivotChart / Changing PivotChart data range | Charts and Charting in Excel | |||
How to Print OLAP pivotchart in Frontpage 2003? | Excel Discussion (Misc queries) | |||
How to execute a macro when data in a pivotchart changes? | Excel Programming | |||
PivotChart formatting: macro for multiple unique charts | Excel Programming |