透视缓存刷新VBA

Question

您好，我有一个数据透视表设置，我需要刷新一个VBA命令。但是，当我刷新pivot命令时，它排除了一些必需的列标签值。我认为我必须更改最初设置的Pivot Cache，但不确定如何更改？(有人能建议如何做到这一点吗？)

我使用的代码如下：

Worksheets("Summary by Account").PivotTables("PivotTable1").RefreshTable

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
For Each pt In ActiveSheet.PivotTables
    pt.ManualUpdate = True
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    For Each pf In pt.PivotFields
        If pf.Orientation <> 0 Then
            If pf.Orientation = xlPageField Then
                pf.CurrentPage = "(All)"
            Else
                For Each pi In pf.PivotItems
                    pi.Visible = True
                Next pi
            End If
        End If
    Next pf
    pt.ManualUpdate = False
Next pt
Set pi = Nothing
Set pf = Nothing
Set pt = Nothing
Set wks = Nothing

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Nominal / Category")
    .PivotItems("(blank)").Visible = False
End With

Answer 1

我正在使用以下代码刷新我的数据透视表缓存，我的源文件位于远程工作簿中，因此您可以根据需要调整参数。

Const Pivot_sht_str             As String = "Summary by Account"
Const Pivot_name_str            As String = "PivotTable1"
Dim pt                          As PivotTable

Dim Data_FilePath               As String
Dim Data_book                   As String
Dim Data_sht_str                As String
Dim Data_sht                    As Worksheet
Dim Data_range                  As Range
Dim Data_range_str              As String
Dim lrow                        As Long

' Asign Pivot table to pt
Set pt = Worksheets(Pivot_sht_str).PivotTables(Pivot_name_str)

' Asign Pivot's Data source parameters >> this part is needed only if source for PIVOT table is data in another workbook
Data_FilePath = "\\Folder\Nested Folder\Nested Folder 2\" 'modify to your needs
Data_book = "File_Name.xlsx" 'modify to your needs
Data_sht_str = "Worksheet Name" 'modify to your needs

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Workbooks.Open (Data_FilePath & Data_book)

Set Data_sht = ActiveWorkbook.Worksheets(Data_sht_str)
lrow = Data_sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Data_range_str = Data_sht.Name & "!" & Range("$A$1:$P$" & lrow).Address '.Address(ReferenceStyle:=xlR1C1)  ' adjust Columns to your needs
Set Data_range = Data_sht.Range(Data_range_str)

' check if Data Range consists of a legal range, Workbook and worksheet are legal
If Not Data_range Is Nothing Then
    Data_range_str = Data_FilePath & "[" & Data_book & "]" & Data_sht.Name & "!" & Range("$A$1:$P$" & lrow).Address

    'Change Pivot Table Data Source Range Address (refresh it's cache data)
    pt.ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, Data_range_str, xlPivotTableVersion12) ' verify last parameter to have the correct Excel version
    pt.RefreshTable
    On Error Resume Next

    ActiveWorkbook.Close (False)  ' Workbooks(Data_FilePath & Data_book).Close savechanges:=False
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True

If Data_range Is Nothing Then Exit Sub

Answer 2

我使用新信息更新数据透视表缓存的方法是将所有数据存储在工作簿中的单独工作表中，并将其放入名为range的扩展范围公式中-使用以下公式

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

然后，在VBA中，使用：

ThisWorkbook.RefreshAll

希望这能有所帮助！

Answer 3

我使用此代码来重置整个工作簿中的所有数据透视表缓存，并防止数据透视表下拉筛选器显示基础数据中实际不存在的缺少项。

Sub PivotCacheReset()
' When a data set is pivoted and then some of the underlying data is
'  removed, even after a refresh old removed values can still remain in the
'  pivot filter.  This macro changes the properties of all pivot tables in
'  the active workbook, to prevent missing items from appearing, or clear
'  items that have appeared. It also resets all pivot table caches in the
'  active workbook.

    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim iWs As Worksheet
    Dim pvt As PivotTable
    Dim iProtectState As Boolean

    ' Loop through each worksheet.
    For Each iWs In wb.Worksheets

        ' If the worksheet is protected, unprotect it. Remember
        '  it was protected so that it can be re-protected later.
        iProtectState = False                    ' Reset variable that remembers if each sheet is protected.
        If iWs.ProtectContents = True Then
            ' Worksheet is protected.
            iWs.Unprotect                        ' Unprotect the worksheet.
            iProtectState = True                 ' Remember that this worksheet was protected.
        End If

        ' Loop through each pivot table in the sheet.
        For Each pvt In iWs.PivotTables
            pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone ' Don't allow missing items in the pivot table filters.
            pvt.PivotCache.Refresh               ' Reset the pivot table cache including any missing items.
        Next pvt

        ' If the worksheet was originally protected, re-protect it.
        If iProtectState = True Then iWs.Protect

    Next iWs

End Sub