7

I need to safely transfer the cache of a Pivot Table on an Excel File, into a pivot on a different file. How can I do this?

This is the code I'm using now (Notice this method works even if the Source pivot Data Source has been eliminated):

Public Sub TransferPivotCache(Source As PivotTable, Target As PivotTable)
    Dim TempSheet As Worksheet
    Set TempSheet = ThisWorkbook.Sheets.Add
    Source.TableRange2.Copy Destination:=TempSheet.Range("A1")
    Target.CacheIndex = TempSheet.PivotTables(1).CacheIndex
    TempSheet.Delete
End Sub

However when the pivot I'm importing is too big I get the error "Not enough memory" when modifying the cache index property. And afterwards, even the file closes and if I try to reopen it, it's corrupted. Is there a better way to transfer a Pivot Cache between pivot tables?

enter image description here

lisovaccaro
  • 32,502
  • 98
  • 258
  • 410
  • Try `ActiveCell.PivotTable.CacheIndex = TempSheet.PivotTables(1).CacheIndex` instead of `TargetPivot.CaceIndex = TempSheet.PivotTables(1).CacheIndex` – Karthick Gunasekaran May 02 '16 at 05:51
  • What Excel version are you using? – Daniel Dušek May 02 '16 at 11:43
  • I'm using Excel 2013 – lisovaccaro May 02 '16 at 14:17
  • how big is your data ? is it possible that it is simply to big ? have you checked your chache size with ActiveWorkbook.PivotCaches(SourcePivot.CacheIndex).MemoryUsed – Doktor OSwaldo May 02 '16 at 15:31
  • I'm sure there's a number of reasons, but why not just delete the original data and save the old Workbook as the new Workbook. The new workbook will have the Pivot Table and the cache. – OldUgly May 03 '16 at 06:25
  • @OldUgly sometimes we need to import multiple pivots from different files into a single workbook. – lisovaccaro May 03 '16 at 22:54
  • According to [MS Documentation](https://msdn.microsoft.com/en-us/library/office/ff841243.aspx) - "the first report’s fields must be a valid subset of the fields in the second report". How do you know the Target report is a valid subset of Source? – OldUgly May 03 '16 at 23:21
  • I am guessing the "There isn't enough memory" message gets generated on the `Source.TableRange2.Copy Destination:=TempSheet.Range("A1")` line and not on the `Target.CacheIndex = TempSheet.PivotTables(1).CacheIndex` line. How big is the source data for the Pivots that generate this error? – OldUgly May 04 '16 at 07:47
  • @DoktorOSwaldo I checked with memory used and it returned 147063128, so around 140MB. Though the file is just above 20MB so I'm not sure how it could be so big. – lisovaccaro May 04 '16 at 11:58

2 Answers2

4

If your goal it to update another pivot table targeting the same data, then another way would be to create a new PivotCache pointing to the same source. This way, the targeted workbook will build the same PivotCache without the need to copy the DataTable, which is probably the cause of your memory issue.

Public Sub TransferPivotCache(source As PivotTable, target As PivotTable)
    Dim pivCache As PivotCache, sh As Worksheet, rgData As Range, refData

    ' convert the `SourceData` from `xlR1C1` to `xlA1` '
    source.Parent.Activate
    refData = Application.ConvertFormula(source.SourceData, xlR1C1, xlA1, xlAbsolute)
    If IsError(refData) Then refData = source.SourceData

    If Not IsError(source.Parent.Evaluate(refData)) Then
        ' create a new pivot cache from the data source if it exists '

        Set rgData = source.Parent.Evaluate(refData)
        If Not rgData.ListObject Is Nothing Then Set rgData = rgData.ListObject.Range

        Set pivCache = target.Parent.Parent.PivotCaches.Create( _
          XlPivotTableSourceType.xlDatabase, _
          rgData.Address(external:=True))

        pivCache.EnableRefresh = False
        target.ChangePivotCache pivCache
    Else
        ' copy the pivot cache since the data source no longer exists '

        Set sh = source.Parent.Parent.Sheets.Add
        source.PivotCache.CreatePivotTable sh.Cells(1, 1)
        sh.Move after:=target.Parent  ' moves the temp sheet to targeted workbook '

        ' replace the pivot cache '
        target.PivotCache.EnableRefresh = True
        target.CacheIndex = target.Parent.Next.PivotTables(1).CacheIndex
        target.PivotCache.EnableRefresh = False

        'remove the temp sheet '
        target.Parent.Next.Delete
    End If

End Sub
Florent B.
  • 41,537
  • 7
  • 86
  • 101
  • hi Florent, I've been testing method A and I kept getting the error `Method 'ChangePivotcache' of object 'PivotTable' failed` on the last line. I'll test the other now. – lisovaccaro May 03 '16 at 12:04
  • I've been testing the second method (both SourceData are xlDatabase) however I get the error "Reference is not valid" on the line `pivCache.SourceData = dataSource`. I tried importing different pivots and both procedures worked for a minority of my pivots, but in most cases I got these errors. – lisovaccaro May 03 '16 at 12:13
  • btw I edited your answer and added a comment on the second block of code. I might be wrong about it. – lisovaccaro May 03 '16 at 12:15
  • Your right, the `target.Parent.Parent.PivotCaches(1)` should be `target.PivotCache`. The error could be due to a space in the dataSource. Can you check that there isn't? It could also be that the pivot source is not compatible with the targeted pivot. Could you provide a failing example? – Florent B. May 03 '16 at 12:21
  • I've made some changes to include the `PivotCache` version. I've also disabled the events. – Florent B. May 03 '16 at 12:47
  • I could identify why these solutions didn't work in some cases. The reason is that many reports I need to import, have a named range as Data Source, but the source data is missing from the report (probably eliminated). So the file only has the Pivot and Pivot Cache and not the source data. The original code I was using worked in these cases (albeit I don't know how). Is there some way to make your solutions work in these cases? – lisovaccaro May 03 '16 at 14:49
  • here's an example of a pivot where the source data is eliminated: https://drive.google.com/file/d/0B9KZr1w4stxPZUEtUGpicy1DNFk/view?usp=sharing – lisovaccaro May 03 '16 at 14:57
  • I've updated the answer to better handle the data source. It now works with a missing data source without duplicating the whole table. – Florent B. May 03 '16 at 23:30
  • @FlorentB. - why not always use the method in "else" block? – OldUgly May 04 '16 at 07:53
  • @OldUgly, The first method doesn't alter the original workbook and will return the refreshed data. The second on is probably less reliable, but you should use the one that best fit your needs. – Florent B. May 04 '16 at 10:49
  • hi Florent, I'm testing this, however the first and second evaluate returns "Object required" due to source.SourceData transforming the range in R1C1 format for some reason. I used a Function to transform the R1C1 to A1 but still I get the error: "Object variable or with block variable not found" when using the property `.ListObject.`. Did you change anything from your previous solutions? Both of them seemed to work when the data source wasn't missing. Could you add them back below your answer? – lisovaccaro May 09 '16 at 12:22
  • hi Florent, I added a ' (quote) before the sourceaddress which fixed the evaluations. Still I'm getting "Invalid procedure call or argument in `PivotCaches.Create(...)` do you know what could be the problem? It's a downer since both of the old solutions you had given me did work perfectly for these cases. – lisovaccaro May 10 '16 at 22:13
  • I added a line to activate the source sheet to allow `ConvertFormula` to add the correct workbook in the address. I've also removed the cache version as you suggested. – Florent B. May 11 '16 at 02:15
0

I was not able to reproduce the resource issue with my Excel Professional 2010... But have you tried this simpler possibility?:

Public Sub TransferPivotCache(SourcePivot As PivotTable, TargetPivot As PivotTable)
  TargetPivot.CacheIndex = SourcePivot.CacheIndex
End Sub

It looks like it does the same (at least within the same workbook), and it avoids creating a whole new sheet.

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
  • I think I tried this and it doesn't work between different workbooks. I'll try it later and confirm. – lisovaccaro May 02 '16 at 14:16
  • I could not make your code work between different workbooks either, but maybe I instantiate source and target pivot wrong. – J. Chomel May 02 '16 at 14:28
  • It does work, it's something like: `Call TransferPivotCache(Workbooks("example").Sheets("example").PivotTables(1), ThisWorkbook.Sheets("example").PivotTables(1))` did you do something different? – lisovaccaro May 02 '16 at 20:49
  • Hum, I think I just got a little mixed up... No my solution only works within same workbook, sorry. – J. Chomel May 03 '16 at 06:24