将数据透视缓存从一个文件上的数据透视表转移到另一个文件上的数据透视表? [英] 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屋!