Excel VBA:错误隐藏数据透视表中的计算的字段 [英] Excel vba: error hiding calculated field in Pivot table

问题描述

我写了几个Subs来显示/隐藏数据透视表中的字段.现在，我试图对计算所得的字段执行相同的操作，但是隐藏它时出现错误.我从记录器中获取了代码，记录器的代码也停在了最后一行.我用错误信息搜索了Google，但没有得到满意的结果.

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

如果MyField是普通字段(而不是计算字段)，则相同的代码也可以正常工作.
我正在使用Excel 2007 SP2.
有任何线索吗?

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

在2010年6月17日进行编辑:我也尝试使用pt.DataFields代替pt.PivotFields，但它们的行为完全相同.错误消息显示无法设置PivotField类的方向".

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".

推荐答案

好，我将给您所需的确认.在计算字段"上使用 Orientation 属性似乎不起作用，我必须同意这是一个错误，而不是常见的使用"错误.我能够复制隐藏/显示"字段，而不必删除(删除")计算出的字段.这使用户可以通过编程方式隐藏"字段，从而从字段列表中物理拖动计算出的字段.这不是一个不好的解决方案，因为它复制了用户界面.(使用Excel 2003.)

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

---

[原始答案]很可能您无法隐藏该项目，因为它是最后可见的项目.相反，请尝试将其删除.

这篇关于Excel VBA:错误隐藏数据透视表中的计算的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！