Pivot Table Programming

[Matt Childs]

I have a pivot table built on a worksheet and I want to pivot it
programatically and grab values to place in models on other sheets.

However, I cannot seem to pivot - I get an error 1004 Unable to set the
orientation property of the PivotField class. According to MS this was
an error in XL 2000 that they fixed. I am using Excel 2003 and can find
no mention of it anywhere. Here is the code:

ub CheckTable()
Dim pvtTable As PivotTable
Dim pvtCache As PivotCache


Set pvtTable = ActiveSheet.PivotTables(1)
Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1)


Debug.Print pvtTable.Name

For Each PivotField In pvtTable.PivotFields
Debug.Print PivotField.Name
Debug.Print PivotField.Orientation
Debug.Print PivotField.Position

Next

For Each DataField In pvtTable.DataFields
Debug.Print DataField.Name
Debug.Print DataField.Orientation
Debug.Print DataField.Position

Next
pvtTable.PivotFields("[Time].[Week].[Week]").Orientation = xlColumnField

End Sub

Everthing works fine until I try to pivot that dimension (This is
reading from a cube on AS). Any ideas on what I might be doing wrong?

Thanks,

Matt Childs
Anchorage, AK.

 

[Haukwa]

Matt,

I was able to get your code to work by doing the following:
- I had to Dim the PivotField and DataField variables. Without a Dim
statement, I was getting a 'Compile Error: Variable not Defined'.
- I had to have a pivot table with a field labeled
"[Time].[Week].[Week]"

After that, the program pivoted the field from a row to a column. The
1004 error told me that the program could not find the field name you
were referencing. That's when I changed one of the labels in my pivot
table.

Does that help?

Gerry

 

[Matt Childs]

Matt,

I was able to get your code to work by doing the following:
- I had to Dim the PivotField and DataField variables. Without a Dim
statement, I was getting a 'Compile Error: Variable not Defined'.
- I had to have a pivot table with a field labeled
"[Time].[Week].[Week]"

After that, the program pivoted the field from a row to a column. The
1004 error told me that the program could not find the field name you
were referencing. That's when I changed one of the labels in my pivot
table.

Does that help?

Gerry

Yep - it does. Followed your steps and got it working. Thanks,

Matt