Excel PivotTable Update - Not Creating PivotCache (VBA)

Question

As EPPlus doesn't support manipulation of pivot table source ranges (more specifically, one can update the cache definition, but this update is not retained once the file is saved), I have had to resort to VBA in the template itself to update source ranges and refresh pivot tables.

There are several pivot tables each for 2 data sources stored in the file and 2 associated PivotCache objects. My goal is to update all pivot tables to new cell ranges in the 2 sources. Since I don't want to duplicate a bunch of PivotCaches to do so, I'm only creating the first one, then attempting to update subsequent pivot tables that share the dataset in question to that same cache.

Here is an excerpt for updating pivots from one of the caches ("downloads"). The rest of the function is doing the exact same thing for the 2nd cache (it's nested within the same loops, but left out for brevity).

Set downloads = ThisWorkbook.Worksheets("DLRaw")
For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        If Not downloadsCreated Then                
            Set startCell = downloads.Range("A8")
            Set endCell = downloads.Range("Y" & startCell.SpecialCells(xlLastCell).Row)
            Set dataRange = downloads.Range(startCell, endCell)
            newRange = downloads.Name & "!" & dataRange.Address(ReferenceStyle:=xlR1C1)

            pt.ChangePivotCache _
            ThisWorkbook.PivotCaches.Create(xlDatabase, newRange)
            Set downloadsCache = pt.PivotCache
            downloadsCreated = True                    
        Else                    
            If pt.CacheIndex <> downloadsCache.Index Then pt.CacheIndex = downloadsCache.Index                    
        End If

        pt.RefreshTable

        For Each rf In pt.RowFields
            If rf.Position <> pt.RowFields.count Then
                rf.ShowDetail = False
            End If
        Next rf
        For Each cf In pt.ColumnFields
            If cf.Position <> pt.ColumnFields.count Then
                cf.ShowDetail = False
            End If
        Next cf
    Next pt
Next ws

I consistently get a run-time error '1004' regarding a null entity, be thrown by the line in the Else block. Stepping through the code, I notice the CacheIndex of the newly-created pivot cache is 0, so either the downloadsCache object isn't be assigned to, or the new cache isn't being created in the first place. I do see correct values in the range variables. This behavior exists both for the excerpt above for downloads as well the 2nd cache's pivot tables.

Any ideas on where to look next, or a different approach to solving this problem if that is necessary?

can you describe better what you are trying to achieve ? can you add the code before If Not downloadsCreated Then , it might allow a better solution for your problem. What exactly are you trying to do with your Else section ? — Shai Rado
– Shai Rado, Commented May 5, 2017 at 18:03
so you're getting straight to the Else which mean somehow downloadsCreated = True , yes ? you ran this code before ? — Shai Rado
– Shai Rado, Commented May 5, 2017 at 18:26
No, the "If Not" block runs and downloadsCache goes from null to populated, however its index property is set to 0, which appears to be invalid. When the Else block runs on the next pass of the loop, I get the null ref error from trying to assign to the next pivot's CacheIndex from downloadsCache. — Alex Jonischkies
– Alex Jonischkies, Commented May 5, 2017 at 18:41
There's another way to update all pivot tables with the same pivot cache, if you want ? — Shai Rado
– Shai Rado, Commented May 5, 2017 at 18:49

Shai Rado · Accepted Answer · 2017-05-06 08:17:01Z

1

Try the code below to update all PivotTables in a sheet with one PivotCache.

Code

Option Explicit

Sub UpdatePivotTables()

Dim ws                  As Worksheet
Dim downloads           As Worksheet
Dim PT                  As PivotTable
Dim PTCache             As PivotCache

Dim startCell As Range, endCell As Range
Dim dataRange As Range
Dim newRange As String

Set downloads = ThisWorkbook.Worksheets("DLRaw")
For Each ws In ActiveWorkbook.Worksheets
    ' the Range setting needs to be done once >> take outside the loop
    Set startCell = downloads.Range("A8")
    Set endCell = downloads.Range("Y" & startCell.SpecialCells(xlLastCell).Row)
    Set dataRange = downloads.Range(startCell, endCell)
    newRange = dataRange.Address(False, False, xlR1C1, xlExternal) '<-- get the range address including the sheet's name (and workbook if needed)

    For Each PT In ws.PivotTables
        ' set the pivot cache
        Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newRange)

        ' update the Pivot Table with the updated Pivot Cache's Source Data
        PT.ChangePivotCache PTCache
        PT.RefreshTable

        Set PTCache = Nothing ' reset for next cycle
    Next PT
Next ws

End Sub

answered May 6, 2017 at 8:17

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Jonischkies Over a year ago

This worked with minor modification to avoid creating 11 caches, but ended up being too slow for this use case. I ended up resolving the problem in .NET, but this is still the most direct solution for anyone who comes looking.

David Buck · Accepted Answer · 2020-11-06 22:17:30Z

-1

Private Sub GenerateReport_Click()

Dim PTable As PivotTable
Dim PCache As PivotCache
Dim PRange As Range
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim LastRow As Long
Dim LastCol As Long

    On Error Resume Next
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Worksheets("Report").Delete
    Sheets.Add(After:=Sheets("CheckList")).Name = "Report"
    ActiveSheet.Name = "Report"
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("Report")
    Set DSheet = Worksheets("Checklist")


    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)



    Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange.CurrentRegion)


    Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="AuditReport")
    
End Sub

edited Nov 6, 2020 at 22:17

David Buck

3,87740 gold badges54 silver badges73 bronze badges

answered Nov 6, 2020 at 21:53

r.morais

1

1 Comment

r.morais Over a year ago

Can someone help me the code above don't work... The code creates the worksheet "reports" but don't create the blank pivot table, any ideias??

Collectives™ on Stack Overflow

Excel PivotTable Update - Not Creating PivotCache (VBA)

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related