excel 如何获取数据透视表类的PivotFields属性？

uqcuzwp8 于 2023-03-04 发布在其他

关注(0)|答案(2)|浏览(217)

我正在寻找一种方法来过滤我的数据透视表基于机器类型（一个字段，我拖到我的数据透视表过滤框）。
我以前用过下面的公式，它起作用了，但是，我得到了
错误1004：无法获取数据透视表类的PivotFields属性。
单元格A1具有要筛选的计算机名称

Sub FilterbyMachine()

    Dim pt As PivotTable
    Set pt = Sheets("Pivottables").PivotTables("MachineStats")

    Dim pf As PivotField
    Set pf = pt.PivotFields("Machine")

    pf.ClearAllFilters

    'slow iterates all items and sets Visible (manual filter)
    Dim pi As PivotItem
    For Each pi In pf.PivotItems
        pi.Visible = (pi.Name = Range("a1"))
    Next

    'fast way sets a label filter
    pf.PivotFilters.Add2 Type:=xlCaptionEquals, Value1:=Range("a1")
    
End Sub

excel

来源：https://stackoverflow.com/questions/58862381/how-do-i-get-the-pivotfields-property-of-the-pivottable-class

2条答案

按热度按时间

kr98yfug1#

你在这一行得到错误？：

Set pf = pt.PivotFields("Machine")

我想可能是因为没有使用该名称的数据透视字段。Like here.
尝试在该行之前添加以下内容，以检查有哪些名称：

For Each pf In pt.PivotFields
            Debug.Print (pf.Name)
    Next pf

编辑其他选项：它可能与透视缓存有关。您可以尝试从这里（下面）在相同的位置调用子缓存。

Sub RefreshAllPivotCaches()
' Developed by Contextures Inc.
' www.contextures.com
Dim wb As Workbook
Dim lPCs As Long
Dim lPC As Long
Dim lProb As Long

Set wb = Application.ThisWorkbook
lPCs = wb.PivotCaches.Count

For lPC = 1 To lPCs
  wb.PivotCaches(lPC).Refresh
  If Err.Number <> 0 Then
    MsgBox "Could not refresh pivot cache " & lPC _
      & vbCrLf _
      & "Error: " _
      & vbCrLf _
      & Err.Description
    Err.Clear
    lProb = lProb + 1
  End If
Next lPC

MsgBox "Refresh is complete. " _
  & vbCrLf _
  & "Pivot Cache Count: " & lPCs _
  & vbCrLf _
  & "Failed refreshes: " & lProb

End Sub

可能没有必要保留它。试着自己运行一次。然后是你的代码。如果这样做不错，你可能想在错误处理中的set pf行附近调用它。以防它再次发生。类似于这样：

Setpf:
Dim CachesCleared as Boolean
On Error Goto ClearPivotCache
Set pf = pt.PivotFields("Machine")
Goto FieldsAreGood ' no need to clear if it's working

ClearPivotCache:
On Error Goto 0 ' reset error handling
If not CachesCleared Then ' only try clearing once, no infinite loops
    Call RefreshAllPivotCaches
    CachesCleared = True
    Goto SetPF
Endif

FieldsAreGood:
On Error Goto 0 ' reset error handling
' The rest of your code