unable to set the orientation property of the pivotfield class
Oscar Márquez
got a pivot table with a pivot chart, with just 2 fields in the Values area,
it is a VS chart where one field is always the same and the other could be on
of three different fields, I added a button to change it to ease the things
for the final user who may not know how to manipulate pivot tables/charts.
but when I run this line:
ActiveSheet.PivotTables("ChartPivot").PivotFields(Field).Orientation =
xlHidden
***Field = current pivot field
It returns the next error: unable to set the orientation property of the
pivotfield class
I searched this in google and found that it was an Excel 2000 problem wich
could be solved by installing the service pack. So i downloaded the service
pack 2 for my Office 2007 but it is still failing, Does anyone knows how to
fix this?? It is driving me crazy!! Thanks in advance for your help.
Kind Regards,
Oscar Marquez
H�ctor Miguel
__ 1 __
> Hi! I'm using Excel 2007 Professional, I'm writing a very simple macro, I've got a pivot table with a pivot chart
> with just 2 fields in the Values area, it is a VS chart where one field is always the same
> and the other could be on of three different fields, I added a button to change it to ease the things
> for the final user who may not know how to manipulate pivot tables/charts. but when I run this line:
> ActiveSheet.PivotTables("ChartPivot").PivotFields(Field).Orientation = xlHidden
>
> ***Field = current pivot field
>
> It returns the next error: unable to set the orientation property of the pivotfield class
1) it seems that your "Field" (***Field = current pivot field)
is NOT part of objects collection to which you could assign the ".Orientation" property
(perhaps this "variable" ? makes a reference to an "inexisting" .PivotField in your PivotTable) (???)
BTW, where/what/... is the source data for your pivot table ?
__ 2 __
> I searched this in google and found that it was an Excel 2000 problem wich could be solved by installing the service pack.
> So i downloaded the service pack 2 for my Office 2007 but it is still failing
> Does anyone knows how to fix this?? It is driving me crazy!! Thanks in advance for your help.
2) the service pack referred by your search in google (I guess) applies only for xl-2000 version
from there, the "bug" it is supposed corrected for later versions
if any doubts (or further information...) would you please comment ?
regards,
hector.
p.s. *IF* you speak only in english, would you feel a little bit more comfortable posting to an english NG ?
(you posted into an spanish news group) -???-
Oscar Márquez
Thanks for your answer, The datasource is an Access 2007 Database, the
name of the field do exists, actually I removed it manually while recording a
macro, then I added it again, ran the recorded macro and it crashes with the
described error. The field I want to remove is actually a calculated field
don't know if it matters.
Regards,
Oscar Marquez
"Héctor Miguel" wrote:
> hi, Oscar Márquez !
H�ctor Miguel
> ... The datasource is an Access 2007 Database, the name of the field do exists
> actually I removed it manually while recording a macro, then I added it again
> ran the recorded macro and it crashes with the described error.
> The field I want to remove is actually a calculated field don't know if it matters.
Debra Dalgleish is one of the pivot gurus in the world: http://www.contextures.com/
she has a page full of information about PT: http://www.contextures.com/tiptech.html
in the "P" segment (Pivot Tables) of you could find: Pivot Tables - Custom Calculations
at the bottom of that page find: http://www.contextures.com/xlPivot10.html#Remove
where it is stated:
"To remove a custom calculation from a pivot table:"
"Click the dropdown arrow on the Data field button"
"Remove the check mark from the custom calculation"
"Note: If you remove an item from the data area, it's removed from the pivot table"
"To replace it, you can drag it back from the pivot table field list"
so, if a custom calculation resides in the ".DataBodyRange" of a PivotTable...
note that this action: "Remove the check mark from the custom calculation"
reffers to the ".Visible" property of a .PivotItem, NOT the ".Orientation" property
or it uses the ".Remove" method and to restore "that" pivotItem you should use de ".Add" method
hth,
hector.
__ previos __
Oscar Márquez
I don't have any dropdown in that area, I don't know if it will
work. Here's a screen shot
http://i689.photobucket.com/albums/vv255/oscarmqz/Pivottable.jpg
Could you give a code example.
Regards,
Oscar Márquez
H�ctor Miguel
> I don't have any dropdown in that area, I don't know if it will work. Here's a screen shot
> http://i689.photobucket.com/albums/vv255/oscarmqz/Pivottable.jpg
> Could you give a code example.
AFAIK, calculated fields are (some kind of) "PivorFields" but from another collection: "CustomCaculations"
so, their properties/methods/... are a little bit different from other "PivotFields" collections
and, as Debra stated in her site: "To remove a custom calculation from a pivot table:"
you need to ".Delete" the (explicit) "PivotField" (CustomField) from the PivotTable
and, to "restore" it, you will need the ".Add" method (to re/build the formula for your calculated field)
I suppose you recorded a macro and tryied to adapt fo a more general situation/loop/... (?)
to provide an example, I would need to have (or re/build) "your"... set of data (also the calculated field formula)
or, you can browse the on-line help {F1} from excel-vba and search for "CustomCalculations" / "PivotFields" / and so on...
hth,
hector.
>>> ... The datasource is an Access 2007 Database, the name of the field do exists
>>> actually I removed it manually while recording a macro, then I added it again
>>> ran the recorded macro and it crashes with the described error.
>>> The field I want to remove is actually a calculated field don't know if it matters.
>>
>> Debra Dalgleish is one of the pivot gurus in the world: http://www.contextures.com/
>> she has a page full of information about PT: http://www.contextures.com/tiptech.html
>> in the "P" segment (Pivot Tables) of you could find: Pivot Tables - Custom Calculations
>> at the bottom of that page find: http://www.contextures.com/xlPivot10.html#Remove
>>
>> where it is stated:
>> "To remove a custom calculation from a pivot table:"
>> "Click the dropdown arrow on the Data field button"
>> "Remove the check mark from the custom calculation"
>> "Note: If you remove an item from the data area, it's removed from the pivot table"
>> "To replace it, you can drag it back from the pivot table field list"
>>
>> so, if a custom calculation resides in the ".DataBodyRange" of a PivotTable...
>> note that this action: "Remove the check mark from the custom calculation"
>> reffers to the ".Visible" property of a .PivotItem, NOT the ".Orientation" property
>> or it uses the ".Remove" method and to restore "that" pivotItem you should use de ".Add" method
>>
>> __ previos __
>>>> __ 1 __
>>>>> Hi! I'm using Excel 2007 Professional, I'm writing a very simple macro, I've got a pivot table with a pivot chart
>>>>> with just 2 fields in the Values area, it is a VS chart where one field is always the same
>>>>> and the other could be on of three different fields, I added a button to change it to ease the things
>>>>> for the final user who may not know how to manipulate pivot tables/charts. but when I run this line:
>>>>> ActiveSheet.PivotTables("ChartPivot").PivotFields(Field).Orientation = xlHidden
>>>>>
>>>>> ***Field = current pivot field
>>>>>
>>>>> It returns the next error: unable to set the orientation property of the pivotfield class
>>>>
>>>> 1) it seems that your "Field" (***Field = current pivot field)
>>>> is NOT part of objects collection to which you could assign the ".Orientation" property
>>>> (perhaps this "variable" ? makes a reference to an "inexisting" .PivotField in your PivotTable) (???)
>>>>
>>>> BTW, where/what/... is the source data for your pivot table ?
>>>>
>>>> __ 2 __
>>>> > I searched this in google and found that it was an Excel 2000 problem wich could be solved by installing the service pack.
>>>> > So i downloaded the service pack 2 for my Office 2007 but it is still failing
>>>> > Does anyone knows how to fix this?? It is driving me crazy!! Thanks in advance for your help.
>>>>
>>>> 2) the service pack referred by your search in google (I guess) applies only to xl-2000 version