一个工作簿中的数据透视表缓存，另一工作簿中的数据透视表 [英] 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屋！