时间戳

首页 > 解决方案 > vba 数据透视表 - 应用程序定义或对象定义错误

excel - vba 数据透视表 - 应用程序定义或对象定义错误

问题描述

我正在尝试使用 excel VBA 创建数据透视表。我了解到这可能很棘手，建议使用 R1C1 参考样式将 PivotRange 调暗为字符串。我已将我编写的代码复制粘贴到一个新的宏中，并且在下面指示的行中出现应用程序定义或对象定义错误。然而，数据透视表已经出现在工作表上，但显然没有生成字段，因为宏拒绝继续。

问题：有谁知道是什么导致了错误？

代码：

    Option Explicit

Public wbTO As Workbook
Public ws As Worksheet
Public LastRow As Long, LastCol As Long

Sub CreatePivot()

Dim wbEFiche As Workbook
    Dim wsEF As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.AskToUpdateLinks = False

Set wbTO = ThisWorkbook
Set ws = wbTO.Sheets(1)
Set wsEF = wbTO.Worksheets("Result")

LastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol))

'Create Pivot table
    Dim PivotRange As String
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    'Due to an excel bug, the range has to be encoded in a string format, using a R1C1 reference style
    PivotRange = ws.Name & "!" & ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol)).Address(ReferenceStyle:=xlA1)
    'Set the pivot cache
    Debug.Print PivotRange
    'Debug.Print result = Sheet1!$A$1:$BH$168
    'ERROR ON THE FOLLOWING LINE
    Set PCache = wbTO.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PivotRange). _
    CreatePivotTable(TableDestination:=wsEF.Cells(12, 7), _
    TableName:="PostOccupationTable")
    'Create a blank pivot table
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=wsEF.Cells(12, 7), TableName:="PostOccupationTable")
    'Insert Row & Column Fields
     With wsEF.PivotTables("PostOccupationTable").PivotFields("IndCategory2")
        .Orientation = xlRowField
        .Position = 1
    End With
    With wsEF.PivotTables("PostOccupationTable").PivotFields("PostCategory2")
        .Orientation = xlColumnField
        .Position = 1
    End With
    'Insert data field
    wsEF.PivotTables("PostOccupationTable").AddDataField ActiveSheet.PivotTables( _
        "PostOccupationTable").PivotFields("StamNr"), "Count of StamNr", xlCount
    'Set filters
    With wsEF.PivotTables("PostOccupationTable").PivotFields("PostCategory2")
        .PivotItems("(blank)").Visible = False
    End With
    With wsEF.PivotTables("PostOccupationTable").PivotFields("IndCategory2")
        .PivotItems("(blank)").Visible = False
    End With
    'Apply Style
    wsEF.PivotTables("PostOccupationTable").TableStyle2 = _
        "PivotStyleMedium2"

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.AskToUpdateLinks = True



End Sub

标签： excelvba

解决方案

改变

Set PCache = wbTO.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PivotRange). _
    CreatePivotTable(TableDestination:=wsEF.Cells(12, 7), _
    TableName:="PostOccupationTable")

进入

Set PCache = wbTO.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PivotRange)

创建数据透视表是在下一行完成的，因此并不是真正需要的。貌似报错了，不知道为什么。。。

推荐阅读