PDA

View Full Version : PivotFields class returns an error when .Function property is set



Butcherk
03-06-2014, 02:32 AM
Hello,

I've been scratching my head about this one. I'm creating some code to automate the creation of my pivot tables. It seems that Excel (2010) won't let me set the Function property of my xlDataFields. Here is the code.



Public Function CreatePivotTable(PivotTableSheetName As String, DataSheetName As String, RowNames As Dictionary, _
ColumnNames As Dictionary, DataNames As Dictionary, FilterNames As Dictionary) As Boolean
Dim myPivotCache As PivotCache
Dim myPivotTable As PivotTable
Dim DataWorkSheet As Worksheet
Dim PivotTableSheet As Worksheet
Dim FirstUnusedRow As Long
Dim i As Integer
Dim myPivotField As PivotField
'Set the data sheets
If Not SheetExists(DataSheetName) Then
CreatePivotTable = False
Exit Function
End If
Set DataWorkSheet = Worksheets(DataSheetName)
'Create the pivot table cache
Set myPivotCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, DataWorkSheet.Range("A1").CurrentRegion.Address)
'Set the pivot table sheet as active
If Not SheetExists(PivotTableSheetName) Then
CreatePivotTable = False
Exit Function
End If
ThisWorkbook.Worksheets(PivotTableSheetName).Activate
'Find the first empty rows
FirstUnusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
FirstUnusedRow = FirstUnusedRow + 10 'Create some space between pivot tables
'Create the Pivot table
Set myPivotTable = ActiveSheet.PivotTables.Add(PivotCache:=myPivotCache, TableDestination:=Range("A" & CStr(FirstUnusedRow)))
'Specify the fields
With myPivotTable
'Rows
For i = 0 To RowNames.Count - 1
.PivotFields(RowNames.Keys(i)).Orientation = xlRowField
Next i

'Columns
For i = 0 To ColumnNames.Count - 1
.PivotFields(ColumnNames.Keys(i)).Orientation = xlColumnField
Next i

'Data
For i = 0 To DataNames.Count - 1
.PivotFields(DataNames.Keys(i)).Orientation = xlDataField
If Not DataNames.Items(i) = "" Then
.PivotFields(DataNames.Keys(i)).Function =xlSum '<------------ ERROR OCCURS HERE
End If
Next i

'Filters
For i = 0 To FilterNames.Count - 1
.PivotFields(FilterNames.Keys(i)).Orientation = xlPageField
If Not FilterNames.Items(i) = "" Then
.PivotFields(FilterNames.Keys(i)).Function = FilterNames.Items(i)
End If
Next i
End With
End Function


I'm getting the error message: "Run-time error 1004, Unable to set the Function Property of the PivotField class."

The funny thing is that I tried to record a macro that did exactly that but it generated the same error when I ran it.
Am I missing something crucial here? I might not have the correct set of References for instance.

Any help would be very much appreciated.

Aflatoon
03-06-2014, 02:55 AM
When you set the orientation to Datafield, the caption changes automatically to "Sum of..." (or "Count of ..." depending on the data) so the next line will fail. You can use something like:


'Data
For i = 0 To DataNames.Count - 1
With .PivotFields(DataNames.Keys(i))
.Orientation = xlDataField
If Not DataNames.Items(i) = "" Then .Function =xlSum
End With
Next i

or you could use the AddDataField method of the pivot table instead.

PS Your next block won't work either since you can't set the Function for a page field.

Butcherk
03-06-2014, 03:02 AM
That works fine! Thanks for the help and the explanation.