编程技术网

关注微信公众号,定时推送前沿、专业、深度的编程技术资料。

 找回密码
 立即注册

QQ登录

只需一步,快速开始

极客时间

数据透视缓存语法错误:Error on Pivot Cache syntax

wishman 数据库/缓存 2022-5-11 10:53 8人围观

腾讯云服务器
数据透视缓存语法错误的处理方法

当宏到达此行时,运行以下代码时出现类型不匹配"错误:

While running the below code "Type mismatch" error appears when the macro reaches this line:

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="ERA_Dashboard") 

如何更正代码上的错误?

How do I correct the error on the code?

Sub Pivot() Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long Application.DisplayAlerts = False Worksheets("Summary").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "Summary" Application.DisplayAlerts = True Set PSheet = Worksheets("Summary") Set DSheet = Worksheets("Content") LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) Sheets("Content").Select Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="ERA_Dashboard") Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="ERA_Dashboard") With ActiveSheet.PivotTables("ERA_Dashboard").PivotFields("Issue Status") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ERA_Dashboard").PivotFields("Issue Status") .Orientation = xlDataField .Position = 1 .Function = xlCount .NumberFormat = "#,##0" .Name = "Issue Status" End With End Sub 

问题解答

无需删除并重新创建摘要"表,只需使用下面的代码即可.下面的代码将使用更新的 PCache 创建(或更新)摘要"工作表中的 PTable 数据透视表.

There is no need to delete and re-create "Summary" sheet, just use the code below. The code below will create (or update) the PTable Pivot-Table in "Summary" worksheet with the updated PCache.

代码

Option Explicit Sub Pivot() Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long Set PSheet = Worksheets("Summary") Set DSheet = Worksheets("Content") With DSheet LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set PRange = .Range("A1").Resize(LastRow, LastCol) ' set data range for Pivot Table End With 'Set the Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, PRange) ' add this line in case the Pivot table doesn't exit >> first time running this Macro On Error Resume Next Set PTable = PSheet.PivotTables("ERA_Dashboard") ' check if "ERA_Dashboard" Pivot Table already created (in past runs of this Macro) On Error GoTo 0 If PTable Is Nothing Then ' create a new Pivot Table in "Summary" sheet Set PTable = PSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=PSheet.Range("A1"), TableName:="ERA_Dashboard") With PTable.PivotFields("Issue Status") .Orientation = xlRowField .Position = 1 End With With PTable.PivotFields("Issue Status") .Orientation = xlDataField .Position = 1 .Function = xlCount .NumberFormat = "#,##0" .Name = "Issue Status" End With Else ' just refresh the Pivot cache with the updated Range PTable.ChangePivotCache PCache PTable.RefreshTable End If End Sub 

编辑1 :

Option Explicit Sub Pivot() Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim LastRow As Long Dim LastCol As Long Set PSheet = Worksheets("Summary") Set DSheet = Worksheets("Content") With DSheet LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set PRange = .Range("A1").Resize(LastRow, LastCol) ' set data range for Pivot Table End With ' Set the Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion14) ' add this line in case the Pivot table doesn't exit >> first time running this Macro On Error Resume Next Set PTable = PSheet.PivotTables("ERA_Dashboard") ' check if "ERA_Dashboard" Pivot Table already created (in past runs of this Macro) On Error GoTo 0 If PTable Is Nothing Then ' create a new Pivot Table in "Summary" sheet Set PTable = PSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=PSheet.Range("A1"), TableName:="ERA_Dashboard") Else ' just refresh the Pivot cache with the updated Range PTable.ChangePivotCache PCache PTable.RefreshTable End If End Sub 

这篇关于数据透视缓存语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程技术网(www.editcode.net)!

腾讯云服务器 阿里云服务器
关注微信
^