views:

2359

answers:

7

I have written several Subs to show/hide fields in a PivotTable. Now I am trying to do the same with a calculated field, but I get an error when hiding it. I took my code from the recorder and the recorder's code also halts on the last line. I googled the error message, without serious result.

Sub PrRemove()
    'remove PR
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("MyPivot")
    pt.PivotFields("MyField").Orientation = xlHidden   '<- here is the error
End Sub

The same code works fine if MyField is a normal field (not a calculated one).
I am using Excel 2007 with SP2.
Any clue ?

EDIT on 17 June 2010: I also tried using pt.DataFields instead of pt.PivotFields, with exactly the same behaviour. The error message says "Unable to set the orientation of the PivotField class".

A: 

Well, I will give you the confirmation you need. It seems using the Orientation property on a "Calulated Field" just does not work, and I would have to agree this is a bug and not a common "usage" error. I was able to duplicate "hiding/showing" the field without having to remove ("Delete") the calculated field. This allows the user to physically drag the calculated field from the field list after you have progammatically "hidden" the field. This is not a bad solution because it duplicates the user-interface. (Using Excel 2003.)

'2009.09.25 AMJ
'work around for
'   1004, Unable to set the Orientation property of the PivotField class
'when setting orientation property to hidden of calculated field, as in
'   ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Field1").Orientation = xlHidden

Public Sub Hide()
'hide the data without removing the calculated field
'   this allows the user to physically drag the
'       calculated field from the field list once we
'       have "hidden" it programmatically.
'   if we use the "delete" method, the field is removed
'       from the pivot table and the field list

    Dim oWS As Worksheet
    Dim oPT As PivotTable
    Dim oPF As PivotField
    Dim oPI As PivotItem

    Set oWS = ActiveSheet
    Set oPT = oWS.PivotTables(1)

    For Each oPF In oPT.DataFields
        If oPF.SourceName = "Field1" Then
            'Stop
            Exit For
        End If
    Next

    Set oPI = oPF.DataRange.Cells(1, 1).PivotItem
    'oPI.DataRange.Select
    oPI.Visible = False

End Sub

Public Sub Show()
'show just reads the pivot field to the data fields

    Dim oWS As Worksheet
    Dim oPT As PivotTable
    Dim oPF As PivotField

    Set oWS = ActiveSheet
    Set oPT = oWS.PivotTables(1)

    For Each oPF In oPT.PivotFields
        If oPF.SourceName = "Field1" Then
            'Stop
            Exit For
        End If
    Next

    oPT.AddDataField oPF

End Sub


[original answer] Most likely you cannot hide this item because it is the last visible item. Instead, try removing it.

AMissico
It is not the last visible. I can do that by hand, record it. It just does not replay.All I found was this thread, suggesting this is a known bug:http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/60fa78ed-0913-4bf5-9c47-2bc0eea59a2c
iDevlop
Delete works, but that deletes the calculated field, which I can't re-add then.
iDevlop
I don't understand why you can re-add it?
AMissico
AMissico: yes, you're right, I could re-add it. But I am looking first for a "clean" solution, before going to the workaround.I really think this is an Excel bug, infact, but I'd like to have come confirmation OR learn something :-)
iDevlop
If you believe it is bug, call Microsoft and there will be no charge. They might even have a fix or proven work around. It does look like a bug considering the link you provided.
AMissico
@AMissico: good idea, I didn't think about that, I lost faith in M$ support long time ago, but I'll give them a try.
iDevlop
I gave you the point since I don't see other solution except waiting a year or 2 for m$ to react.
iDevlop
Well, I will give you some confirmation. I was not able to find a "clean" solution. It seems using the Orientation property on a Calulated Field just does not work, and I would have to agree this is a bug and not a "usage" error. I was able to duplicate "hiding" the field without having to remove ("Delete") the calculated field, and have updated my answer.
AMissico
A: 

Have you changed the name of the calculated field? Was it originally 'Sum of MyField'? Try looking at the SourceName property and if it's different using that.

Have you tried pt.CalculatedFields("MyField").Orientation = xlHidden ?

DaveParillo
I have tried with both. I had already done this with other fields with no problem. It only bugs with the calculated field. ANd the strange think is that recorded macro does not replay !!
iDevlop
By any chance, when recording the macro, did you select exactly the cells to be in the pivot table, or did you select the entire sheet or entire columns? I have noticed that when you select the range in this way the macro records incorrectly.
DaveParillo
@DaveParillo: I recorded ok. I had already coded for 2 "real" fields" successfully, and I copy/pasted the same code. The problem just happens with the calculated field.
iDevlop
A: 

I don't think this is an excel bug, I think it's a 'feature'. ;-)

Re: @AMissico, there is no problem in excel hiding all of the fields in a pivot table, but he may be talking about items - you can't hide the last item in a pivot field.

This is the code I routinely use to do what you are trying to do. These macros were developed on Excel 2002 & 2003. I don't hide CalculatedFields, I delete them.

' Hide all fields.
' @param ThePivotTable to operate upon.
Sub HidePivotFields(ByVal ThePivotTable As PivotTable)
    Dim pField As PivotField
    For Each pField In ThePivotTable.CalculatedFields
        pField.Delete
    Next pField
    For Each pField In ThePivotTable.PivotFields
        pField.Orientation = xlHidden
    Next pField

    Set pField = Nothing
End Sub

' Removes FieldName data from ThePivotTable
Sub HideField(ByVal ThePivotTable As PivotTable, _
              ByVal FieldName As String)
    If FieldExists(ThePivotTable, FieldName) = True And _
       CalculatedFieldExists(ThePivotTable, FieldName) = False Then
        ThePivotTable.PivotFields(FieldName).Orientation = xlHidden
    End If
End Sub

' Returns True if FieldName exists in ThePivotTable
'
' @param ThePivotTable to operate upon.
' @param FieldName the name of the specific pivot field.
Function FieldExists(ByVal ThePivotTable As PivotTable, _
                     ByVal FieldName As String) As Boolean
    Dim pField As PivotField

    For Each pField In ThePivotTable.PivotFields
        If pField.SourceName = FieldName Then
            FieldExists = True
            Exit For
        End If
    Next pField

    Set pField = Nothing
End Function

' Checks if the field FieldName is currently a member of the
' CalculatedFields Collection in ThePivotTable.
' @return True if a CalculatedField has a SourceName matching the FieldName
' @return False otherwise
Function CalculatedFieldExists(ByVal ThePivotTable As PivotTable, _
                               ByVal FieldName As String) As Boolean
    Dim pField As PivotField

    CalculatedFieldExists = False

    For Each pField In ThePivotTable.CalculatedFields
        If pField.SourceName = FieldName Then
            CalculatedFieldExists = True
        End If
    Next pField
    Set pField = Nothing
End Function

' Returns a Pivot Field reference by searching through the source names.
'
' This function is a guard against the user having changed a field name on me.
' @param ThePivotTable to operate upon.
' @param FieldName the name of the specific pivot field.
Function GetField(ByVal ThePivotTable As PivotTable, _
                  ByVal FieldName As String) As PivotField
    Dim pField As PivotField

    For Each pField In ThePivotTable.PivotFields
        If pField.Name <> "Data" Then
            If pField.SourceName = FieldName Then
                Set GetField = pField
                Exit For
            End If
        End If
    Next pField

    Set pField = Nothing
End Function

' Counts the number of currently visible pivot items in a field.
' @param ThePivotItems the collection of pivot itemns in a field.
' @return the count of the visible items.
Function PivotItemCount(ByVal ThePivotItems As PivotItems) As Long
    Dim pItem As PivotItem
    Dim c As Long

    For Each pItem In ThePivotItems
        If pItem.Visible = True Then c = c + 1
    Next pItem
    PivotItemCount = c
    Set pItem = Nothing
End Function

' Hides a single pivot item in a pivot field, unless it's the last one.
' @param FieldName pivot field containing the pivot item.
' @param ItemName pivot item to hide.
Sub HidePivotItem(ByVal ThePivotTable As PivotTable, _
                  ByVal FieldName As String, _
                  ByVal ItemName As String)
    Dim pField As PivotField

    Set pField = GetField(ThePivotTable, FieldName)
    If Not pField Is Nothing Then
        If PivotItemCount(pField.PivotItems) > 1 Then
            pField.PivotItems(ItemName).Visible = False
        End If
    End If

    Set pField = Nothing
End Sub
DaveParillo
"you can't hide the last item in a pivot field." Isn't that what I said?
AMissico
Yes, but the question was about a PivotField, not a PivotItem. So I thought your reference to 'this item' was referring to @Patrick Honorez's code, so I was just trying to clarify.
DaveParillo
@DaveParillo, Got it. It would have been clearer if I said, "you cannot hide this pivot field because it must have at least one visible item. For reference, in code, this condition generates an error. In Excel's user-interface, the user is prompted with "At least one item must be selected for display in this field." if they unselect all items for a field.
AMissico
A: 

I am having the exact same problem as you. It looks like I'm going to have to delete the calculated field and readd it rather than hiding/showing it.

Sebastian
Good to know. It seems to be the only solution to that Excel "inconsistency".
iDevlop
A: 

I accidentally discovered a workaround to this the first time I attempted to hide a calculated field, so thought I would share it here:

Instead of modifying the orientation property, you can instead instruct the code to select the cell in the pivot table that contains the title of the calculated field you want to hide, and then delete the selection. This works as long as you have another datafield already in the table. Example below:

Scenario: Pivot table covers the range A1:G10. Calculated field "Margin" is already in the table, and you want to add the data field "Sales" and remove the "Margin" calc field.

Code to execute:

'Add Sales data field
 ActiveSheet.PivotTables(Pname).AddDataField ActiveSheet.PivotTables( _
    Pname).PivotFields("SALES"), "Sum of SALES", xlSum

 'At this point, the datafield titles are in vertically adjacent rows, named "Sum
 'of Margin" and "Sum of Sales" at locations B3 and B4 respectively.

 'Remove the "Sum of Margin" calculated field
  Range("B3").Delete

Not sure why this works, but I'm glad we at least have this to work with!

Jeremy B
Jeremy B: this is not really a calculated field. If you already have Units field and Cost field, then having an Amount field = Units * Costs, that is a calculated field. But thanks anyway for the trick.
iDevlop
A: 

Fortunately there is a very easy way to hide a datafield. You were all wrong mistakeing pivotfields with datafields. I'm presenting a piece of code that empties a pivot table nomather how many pivot fields/data fields were initialy in the pivot :

Sub Macro1()

Dim p As PivotTable Dim f As PivotField

Set p = ActiveSheet.PivotTables(1)

For Each f In p.PivotFields

If f.Orientation <> xlHidden Then f.Orientation = xlHidden End If

Next f

For Each f In p.DataFields

If f.Orientation <> xlHidden Then f.Orientation = xlHidden End If

Next f

End Sub

Sorry for my english, happy coding

Alinboss
@Alinboss: that looks really interesting! I'll check out and come back to you. Thanks for the info.
iDevlop
@Alinboss: your remark about datafields was right, but unfortunately that does not solve the problem on *calculated* fields (see question edits)
iDevlop
Hy, i don't understand, datafields are calculated fields, i applied this code on a huge variety of pivot tables and it clears all the datafields/pivotfields/calculatedfields.Pay attention it doesn't delete the pivot table only clears it.Did you tried my code and it didn't work?
Alinboss
A: 

after much hair pulling i have found a workaround. if you add more than one pivot field (calculated or otherwise) excel creates a grouped field called Values. you can set the orientation property of PivotField("Values") to xlHidden and it bullets both fields. So if you want to remove a calculated field, just add a non-calculated field, set PivotField("Values").orientation to xlHidden and you're done.

nobody said it would be pretty...

adamr
Thanks ! I'll give it a try in a few days. It's never too late :-)
iDevlop