将数据透视缓存从一个文件上的数据透视表转移到另一个文件上的数据透视表? [英] Transfer Pivot Cache from a Pivot on one file to a Pivot on another?

问题描述

我需要安全地将Excel文件上数据透视表的缓存转移到另一个文件上的数据透视表中.我该怎么办?

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?

推荐答案

如果您的目标是更新另一个针对相同数据的数据透视表，则另一种方法是创建指向相同源的新PivotCache.这样，目标工作簿将无需复制DataTable即可构建相同的PivotCache，这可能是导致内存问题的原因.

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

这篇关于将数据透视缓存从一个文件上的数据透视表转移到另一个文件上的数据透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！