Unable to set Orientation property of the PivotField class

[Orfil]

Hi, guys

I have a real nuisance here, a subroutine that executes if I step through it with F8 but crashes with this error message, "Unable to set Orientation property of the PivotField class" if I run it with F5.

'           delete current datafields
            For Each pf In pvt.DataFields
                pf.Orientation = xlHidden
            Next pf

'           delete row & column fields
            For Each pf In pvt.PivotFields
                pf.Orientation = xlHidden <------ this is the problem line
            Next pf

I didn't have this problem with Excel 2013. It only started since a clean install of Excel 2016. Is this a coincidence or did I miss a setting somewhere?

Any comments, remarks, feedback, etc. would be greatly appreciated.

Warm regards and best wishes

Orfil

 

[Chihiro]

Without sample workbook along with explanation of purpose & expected end result. It's difficult to say.

Keep in mind there's difference between PivotTable based on Excel Table and PivotTable based on OLAP.

 

[Debaser]

If you intend to clear the entire table, you could just use:

pvt.ClearTable

instead of looping.

 

[Orfil]

Without sample workbook along with explanation of purpose & expected end result. It's difficult to say.

Keep in mind there's difference between PivotTable based on Excel Table and PivotTable based on OLAP.

As

Without sample workbook along with explanation of purpose & expected end result. It's difficult to say.

Keep in mind there's difference between PivotTable based on Excel Table and PivotTable based on OLAP.

The dashboard runs fine on my laptop. It's only on my desktop PC that I get an error, which makes me think that it's an application problem rather than a code problem. My guess is that the pivot cache is somehow out of sync with the data although I've tried refreshing the affected pivot tables as well as the pivot cache, to no avail.

Any suggestions as to how I might physically delete the current cache and rebuild it would be greatly appreciated.

Oh and, by the way, the pivot tables are based on an Excel Table, not OLAP.

Warm regards & best wishes

Orfil

 

[Orfil]

If you intend to clear the entire table, you could just use:

pvt.ClearTable

instead of looping.

Thank you, debaser, but cleartable wipes fields I need, otherwise that would definitely be an option.

Warm regards & best wishes

Orfil

 

[Chihiro]

Then, I'd suggest following workaround.

Wipe all fields using Debaser's suggested method. Then add the fields you need back into appropriate fields.

See if this works.

 

[Orfil]

Then, I'd suggest following workaround.

Wipe all fields using Debaser's suggested method. Then add the fields you need back into appropriate fields.

See if this works.

For the sake of closure:

Turns out Excel had done its trick of duplicating and renaming a pivot table field name (year becomes year and year1) thereby changing the number of fields and invalidating the loop conditions.

However, before I could fix this, I encountered another problem: the pivot table field list had completely disappeared from view, even though it was turned on on the ribbon.

It took a while but eventually I found out that the excel15.xlb file had been duplicated when I upgraded from a previous version. Once I deleted them, Excel created a new xlb file and the field list reappeared, displaying normally i.e. docked on the right-hand side of the window.