一个工作簿中的数据透视表缓存,另一工作簿中的数据透视表 [英] Pivot Cache in one one workbook, Pivot table in another workbook
问题描述
我已经在工作表'sourceSheet'中的工作簿'sourceBook.xlsx'中创建了数据透视缓存.我正在尝试在工作表"destinSheet"中的另一个工作簿"destinBook.xlsx"中创建数据透视表.
I have created a Pivot Cache in (say)Workbook 'sourceBook.xlsx' in sheet 'sourceSheet'. I am trying to create a Pivot table in a different workbook 'destinBook.xlsx' in sheet 'destinSheet'.
Dim pCache As PivotCache
Dim pTable As PivotTable
Dim pRange As Range
Module2.hc_book.Activate
Set pRange = ActiveSheet.Range(hc_pidCol & "1:" & hc_pidCol & hc_lastRow)
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, pRange)
Module2.mt_book.Activate
Set tempSheet = Worksheets.Add
tempSheet.Select
Set pTable = ActiveSheet.PivotTables.Add(pCache, Range("A1"), "MyPivotTable")
代码在最后一行-我设置pTable的无效过程调用或参数"中给了我错误.该代码在同一张纸上可以很好地用于目的地.所以,请让我知道我在哪里犯错.
The code is giving me error in the last line - "Invalid procedure call or argument", where I am setting the pTable. The code works fine for destination in the same sheet. So, please let me know where I am making the mistakes.
推荐答案
PivotCache
是PivotTable
报告的内存缓存.首先,需要此内存缓存才能使PivotTable
起作用.
A PivotCache
is the memory cache for a PivotTable
report. This memory cache is needed for the PivotTable
to function in the first place.
您可以根据当前工作簿中的数据创建一个PivotCache
,但是它必须是新工作簿中PivotCache的一部分,才能基于该表创建一个PivotTable.
You could create a PivotCache
from data in the current workbook, but it would have to be part of the PivotCaches in the new workbook, in order to create a PivotTable based of off it.
由于PivotCache在新的Workbook.PivotCaches
中不可用,因此您无法在该工作簿中创建数据透视表,这就是为什么代码无法运行的原因.
Since the PivotCache is not available in the new Workbook.PivotCaches
, you cannot create a PivotTable in that workbook of off it and that's why your code doesn't run.
运行良好:
Sub test()
Dim wb As Workbook
Dim ws As Worksheet
Dim pRange As Range
Dim pc As PivotCache
Dim pt As PivotTable
Set wb = Workbooks.Add
Set ws = wb.Worksheets(1)
Set pRange = ThisWorkbook.Sheets("Sheet1").Range("A1:C3")
Set pc = wb.PivotCaches.Create(xlDatabase, pRange)
Set pt = ws.PivotTables.Add(pc, Range("F2"), "MyPivotTable")
End Sub
这不起作用:
Sub test()
Dim wb As Workbook
Dim ws As Worksheet
Dim pRange as Range
Dim pc As PivotCache
Dim pt As PivotTable
Set wb = Workbooks.Add
Set ws = wb.Worksheets(1)
Set pRange = ThisWorkbook.Sheets("Sheet1").Range("A1:C3")
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, pRange) 'Cache in ThisWorkbook
Set pt = ws.PivotTables.Add(pc, Range("F2"), "MyPivotTable") 'Cache unavailable, error 5 - Invalid Procedure Call or Argument.
End Sub
此错误的无效参数是pc
对象.
The invalid argument for this error is the pc
object.
简而言之:PivotCache
对象必须是要创建PivotTable
In short: PivotCache
object needs to be part of the PivotCaches
collection of the Workbook
where you wish to create the PivotTable
编辑:只需说明一下:PivotCache
是内存中对象.它与您从中获取数据的源无关.不管您选择什么,此来源的确可以是第一个工作簿中的范围,也可以是SQL查询的结果或CSV文件.
Just to clarify: A PivotCache
is an in memory object. It has nothing to do with the source where you got the data from. This source can indeed be the range in your first workbook, or the results of a SQL query, or a CSV-file, whatever you choose.
一个非常基本的将" pivotCache复制"到新工作簿的实现是:
EDIT 2: A very basic implementation of "copying" a pivotCache to a new workbook would be:
Sub CopyPivotCache()
Dim wb As Workbook
Dim InitialPivotCache As PivotCache
Dim CopyPivotCache As PivotCache
Set wb = Workbooks.Add
Set InitialPivotCache = ThisWorkbook.PivotCaches(1)
Set CopyPivotCache = wb.PivotCaches.Create(InitialPivotCache.SourceType, InitialPivotCache.SourceData)
End Sub
这篇关于一个工作簿中的数据透视表缓存,另一工作簿中的数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!