如何在 VBA 中创建数据透视表

How to Create a Pivot Table in VBA

提问by aSystemOverload

I'm trying to create a Pivot table, but getting Invalid Procedure Call or Argument.

我正在尝试创建一个数据透视表，但得到Invalid Procedure Call or Argument.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="rng", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:="rngB", TableName:="pvtReportA_B", DefaultVersion:=xlPivotTableVersion14

• rng(The source) is a range consisting of about 20 columns and a few thousand rows.
• rngB(The destination) is a single cell in a different worksheet

• rng（源）是一个由大约 20 列和几千行组成的范围。
• rngB（目标）是不同工作表中的单个单元格

Can anyone advise where I am going wrong?

谁能告诉我哪里出错了？

EDIT:

编辑：

My fault, I should have been using rngDataand not rngas the Source.

我的错，我应该一直使用rngData而不是rng作为源。

    Set rng = wsA.Range("C14")
    Set rngData = Range(rng, rng.End(xlToRight))
    Set rngData = Range(rng, rng.End(xlDown))
    Set rngB = wsB.Range("C8")

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=rngB, TableName:="pvtReportA_B", DefaultVersion:=xlPivotTableVersion14

This brings up the PivotTable frame just fine.

这会很好地显示数据透视表框架。

采纳答案by aSystemOverload

In this instance, I used the wrong range object, which caused Excel to throw a fit.

在这种情况下，我使用了错误的范围对象，这导致 Excel 出现异常。

Set rng = wsA.Range("C14")
Set rngData = Range(rng, rng.End(xlToRight))
Set rngData = Range(rng, rng.End(xlDown))
Set rngB = wsB.Range("C8")

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=rngB, TableName:="pvtReportA_B", DefaultVersion:=xlPivotTableVersion14

回答by Nicolaesse

To create a pivot in Excel 2010, using VBA code, you can use and adapt this template:

要在Excel 2010中使用 VBA 代码创建数据透视表，您可以使用并调整此模板：

Sub newPVT()
    Dim PTCache As PivotCache
    Dim PT As PivotTable

    'Create the Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=Range("Dynamic_Field_Summary"))

    'Select the destination sheet
    Sheets("Field Summary").Select

    'Create the Pivot table
    Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _
        TableDestination:=Range("P1"), TableName:="Pivot1")

    ActiveWorkbook.ShowPivotTableFieldList = True

    'Adding fields
    With PT
        With .PivotFields("Enterprise")
            .Orientation = xlColumnField
            .Position = 1
        End With

        With .PivotFields("Field")
            .Orientation = xlRowField
            .Position = 1
        End With

        With .PivotFields("Planted Acres")
            .Orientation = xlDataField
            .Position = 1
            .Caption = " Planted Acres"
            .Function = xlSum
        End With

        With .PivotFields("Harvested Acres")
            .Orientation = xlDataField
            .Position = 2
            .Caption = " Harvested Acres"
            .Function = xlSum
        End With

        With .PivotFields("lbs")
            .Orientation = xlDataField
            .Position = 3
            .Caption = " lbs"
            .Function = xlSum
        End With

        'Adjusting some settings
        .RowGrand = False
        .DisplayFieldCaptions = False
        .HasAutoFormat = False

        'Improving the layout
        .TableStyle2 = "PivotStyleMedium9"
        .ShowTableStyleRowStripes = True
        .ShowTableStyleColumnStripes = True

    End With

    With ActiveSheet
        'Adjusting columns width
        .Columns("P:V").ColumnWidth = 16
        .Range("Q2:V2").HorizontalAlignment = xlCenter
    End With

    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

I found it here.

我在这里找到了。

In this pageyou can also fine the meaning of every part of the code, for example is explained here. I think that this is a good code also to start creating vba macros for Excel 2007 or other versions.

在这个页面中你还可以细化代码中每一部分的含义，例如在这里解释。我认为这也是一个很好的代码，可以开始为 Excel 2007 或其他版本创建 vba 宏。