时间戳

首页 > 解决方案 > 将数据透视表添加到打开的工作簿

excel - 将数据透视表添加到打开的工作簿

问题描述

我已经对如何将数据透视表添加到另一个工作簿进行了大量研究，但我做不到。

我的数据集

宏存储在ThisWorkbook

数据透视表的源数据存储在打开工作簿 - 工作表名称“原始数据”中

数据透视表的目的地需要在同一个打开的工作簿上，但在工作表名称“索赔摘要”上

警告

这procedure是一个更广泛的项目的一部分。因此，我有一些module declarations：

Private wbReconcile As Workbook

Private wsRawData As Worksheet

Private wsSummary As Worksheet

然后将它们设置在不同的过程中（在同一模块中），如下所示：

Set wbReconcile = Workbooks.Open(RECON_FILE, False)

Set wsRawData = wbReconcile.Sheets("Raw Data")

Set wsSummary = wbReconcile.Sheets("Claim Summary")

问题与错误

我遇到的问题是，无论我是使用 myvariables还是输入工作簿的名称、工作表的名称等。我总是在Set myPivotTable错误消息行出现错误：运行时错误“5”：无效过程调用或参数。

我的代码

Sub CreatePivotTable()

    Dim myDestinationWorkbook As Workbook
    Set myDestinationWorkbook = Workbooks("Template_Promo Claims Reconciliation.xlsm")
'    Set myDestinationWorkbook = wbReconcile

    Dim mySourceWorksheet As Worksheet
    Set mySourceWorksheet = myDestinationWorkbook.Worksheets("Raw Data")
'    Set mySourceWorksheet = wsRawData
    Dim myDestinationWorksheet As Worksheet
    Set myDestinationWorksheet = myDestinationWorkbook.Worksheets("Claim Summary")
'    Set myDestinationWorksheet = wsSummary

    'obtain address of destination cell range
    Dim myDestinationRange As String
    myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)

    Dim mySourceData As String
    mySourceData = mySourceWorksheet.Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

    'create Pivot Table cache and create Pivot Table report based on that cache
    Dim myPivotCache As PivotCache
    Set myPivotCache = _
        myDestinationWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="[" & myDestinationWorkbook.name & "]" & _
        mySourceWorksheet.name & "!" & _
        mySourceData)

    Dim myPivotTable As PivotTable
    Set myPivotTable = myPivotCache.CreatePivotTable(TableDestination:="[" & _
        myDestinationWorkbook.name & "]" & myDestinationWorksheet.name & "!" & _
        myDestinationRange, TableName:="RawDataPivot")

End Sub

编辑以显示如何使用答案

Sub AnswerPT()

    Dim pc As PivotCache, pt As PivotTable
    Dim wsSrc As Worksheet, wsDest As Worksheet
    Dim rngSrc As Range, rngDest As Range

    Set wsSrc = wsRawData
    Set wsDest = wsSummary   'for convenience here

    Set rngSrc = wsSrc.Range("A1").CurrentRegion  'source data
    Set rngDest = wsDest.Range("A1")    'table location

    Set pc = wsDest.Parent.PivotCaches.Create(SourceType:=xlDatabase, _
                            SourceData:=rngSrc, Version:=6)

    Set pt = pc.CreatePivotTable(TableDestination:=rngDest, _
                          TableName:="PivotTable2", DefaultVersion:=6)

End Sub

标签： excelvbapivot-table

解决方案

您可以直接使用范围，而无需构造它们的“字符串化”版本：

Dim pc As PivotCache, pt As PivotTable
Dim wsSrc As Worksheet, wsDest As Worksheet
Dim rngSrc As Range, rngDest As Range

Set wsSrc = ActiveSheet
Set wsDest = ActiveSheet   'for convenience here

Set rngSrc = wsSrc.Range("C5:E14")  'source data
Set rngDest = wsDest.Range("I5")    'table location

Set pc = wsDest.Parent.PivotCaches.Create(SourceType:=xlDatabase, _
                        SourceData:=rngSrc, Version:=6)

Set pt = pc.CreatePivotTable(TableDestination:=rngDest, _
                      TableName:="PivotTable2", DefaultVersion:=6)

推荐阅读