VBA:对象"_Chart"的方法"SetSourceData"对于多个数据透视图失败 [英] VBA: Method 'SetSourceData' of object '_Chart' failed For Multiple Pivot Charts

问题描述

这是上一个问题的第二部分:

This is part 2 of the previous question:

VBA:运行时错误-第二个数据透视图的SetSourceData

在遵循该线程提供的(好的)建议之后，我仍然遇到相同的问题.在第二个图表尝试SetSourceData之后，标题错误仍然显示.如果我添加一个On Error Resume Next，则数据变得不稳定且不可靠.我开始认为这是PivotCache的问题，但是我尝试用不同的变量名来区分缓存.这没有解决问题.以下是完整的代码，其中已删除一些标题/名称:

After following the (good) advice offered in that thread, I am still having the same problem. The title error is still showing after the second chart attempts to SetSourceData. If I add an On Error Resume Next, then the data gets wonky and unreliable. I'm starting to think it's a problem with the PivotCache, but I've attempted to differentiate the caches with different variable names. This did not solve the issue. Below is the code in it's entirety with some titles/names redacted:

Option Explicit

Sub CreatePivots()

Call CreateBarPivot
Call CreatePiePivot

End Sub
Sub CreateBarPivot()

Dim myWB As Workbook
Dim PSheet, DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Dim ChartWidth As Range
Dim BPivot As Shape

'Define Workbook
Set myWB = ThisWorkbook

'Define worksheets
Set PSheet = myWB.Sheets("Tools")
Set DSheet = myWB.Sheets("Aggregate")

'Define last data points
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Selects first to last filled row, and first to last filled column for data
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Create pivot cache
Set PCache = myWB.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange)

'Create pivot table
Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Range("A1"), _
        TableName:="ExcPT")

'Create pivot chart
Set BPivot = PSheet.Shapes.AddChart
    BPivot.Chart.SetSourceData Source:=Range("$A$1:$C$18"), PlotBy:=xlRows
    BPivot.Chart.ChartType = xlColumnStacked

ActiveWorkbook.ShowPivotTableFieldList = False

With BPivot.Chart.PivotLayout.PivotTable.PivotFields("redacted")
    .Orientation = xlPageField
    .Position = 1
End With

With BPivot.Chart.PivotLayout.PivotTable.PivotFields("redacted")
    .Orientation = xlColumnField
    .Position = 1
End With

With BPivot.Chart.PivotLayout.PivotTable.PivotFields("redacted")
    .Orientation = xlRowField
   .Position = 1
End With

With BPivot.Chart.PivotLayout.PivotTable.PivotFields("redacted")
    .Orientation = xlRowField
    .Position = 2
End With

'Insert Data
With PSheet.PivotTables("ExcPT").PivotFields("Exception")
    .Orientation = xlDataField
    .Position = 1
    .Caption = "Exception Status Count"
    .Function = xlCount
End With

'Hide Not Due
With BPivot.Chart.PivotLayout.PivotTable.PivotFields("Exception Status")
    .PivotItems("Not due").Visible = False
End With

'Move bar chart to Dashboard; resize
Set ChartWidth = Sheets("Dashboard").Range("B2:L25")
With BPivot.Chart.Parent
    .Height = ChartWidth.Height
    .Width = ChartWidth.Width
    .Top = ChartWidth.Top
    .Left = ChartWidth.Left
End With

BPivot.Chart.ChartArea.Select
BPivot.Chart.Location Where:=xlLocationAsObject, Name:="Dashboard"


End Sub

Sub CreatePiePivot()

Dim myWB As Workbook
Dim PSheet, DSheet As Worksheet
Dim PCache1 As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Dim ChartWidth As Range
Dim PPivot As Shape

'Define Workbook
Set myWB = ThisWorkbook

'Define worksheets
Set PSheet = myWB.Sheets("Tools")
Set DSheet = myWB.Sheets("Aggregate")

'Define last data points
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Selects first to last filled row, and first to last filled column for data
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Create pivot cache
Set PCache1 = myWB.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange)

'Create pivot table
Set PTable = PCache1.CreatePivotTable _
    (TableDestination:=PSheet.Range("F1"), _
        TableName:="ExcPT1")

'Create pivot chart
Set PPivot = PSheet.Shapes.AddChart
    PPivot.Chart.SetSourceData Source:=Range("$F$1:$H$18"), PlotBy:=xlRows
    PPivot.Chart.ChartType = xlPie

ActiveWorkbook.ShowPivotTableFieldList = False

'Insert row
With PPivot.Chart.PivotLayout.PivotTable.PivotFields("Exception")
    .Orientation = xlRowField
   .Position = 1
End With

'Insert Data
With PSheet.PivotTables("ExcPT1").PivotFields("Exception")
    .Orientation = xlDataField
    .Position = 1
    .Caption = "Exception Status Count"
    .Function = xlCount
End With

'Hide Not Due
With PPivot.Chart.PivotLayout.PivotTable.PivotFields("Exception Status")
    .PivotItems("Not due").Visible = False
End With

'Move pie chart to Dashboard; resize
Set ChartWidth = Sheets("Dashboard").Range("B26:L49")
With PPivot.Chart.Parent
    .Height = ChartWidth.Height
    .Width = ChartWidth.Width
    .Top = ChartWidth.Top
    .Left = ChartWidth.Left
End With

PPivot.Chart.ChartArea.Select
PPivot.Chart.Location Where:=xlLocationAsObject, Name:="Dashboard"

With ActiveChart
    .HasTitle = False
End With

End Sub

推荐答案

我遇到了类似的问题.我通过不设置任何变量来解决此问题，这些变量保存了数据透视表和以前使用的数据透视表(在添加第二张图表之前).

I had similar problem. I solved it by setting to nothing variables holding pivotcache and previously used pivottables (before adding second chart).

这篇关于VBA:对象"_Chart"的方法"SetSourceData"对于多个数据透视图失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！