I am implementing a tool that generates PivotTables and Charts from data I put into Excel using a C# program.
Generating the PivotTable from the data works correctly, but when I try to set this PivotTable as the data for my Chart I get the error: Object variable or With block variable not set.
I have searched far and wide, but did not manage to find a solution regarding this error that helped me solve my problem. The error occurs on this line of code, where objChart is a newly created chart object and pt is my pivottable:
objChart.SetSourceData Source:=pt.TableRange1
The full code for creating the pivottable and chart:
Sub GenerateTop5FaultsTrend()
On Error GoTo Errorcatch
'Declare some variables to be used
Dim wsTarget As Worksheet
Dim rngSource As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim field As PivotField
Dim objChart As Chart
Dim objSelect As Range
'Set source and target sheet
Set rngSource = Sheets("MergedData").ListObjects("Table1").Range
Set wsTarget = Sheets("Top5FaultsTrend")
'Delete all pivottables in the target sheet
For Each xPT In Worksheets("Top5FaultsTrend").PivotTables
Worksheets("Top5FaultsTrend").Range(xPT.TableRange2.Address).Delete Shift:=xlUp
Next
'Create a PivotTable with the data from the previously created table
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, rngSource, xlPivotTableVersion14)
Set pt = pc.CreatePivotTable(wsTarget.Range("A1"), "PivotTable1", , xlPivotTableVersion14)
'Set all columns and rows for the pivottable
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("Week")
field.Orientation = xlColumnField
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("ErrorCode")
field.Orientation = xlRowField
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("Vehicle")
field.Orientation = xlDataField
wsTarget.PivotTables("PivotTable1").PivotFields("ErrorCode").PivotFilters. _
Add2 Type:=xlTopCount, DataField:=wsTarget.PivotTables("PivotTable1"). _
PivotFields("Count of Vehicle"), Value1:=5
For Each testChart In wsTarget.ChartObjects
If testChart.Name = "Chart 1" Then
wsTarget.ChartObjects("Chart 1").Delete
End If
Next
Set ojbChart = wsTarget.Shapes.AddChart2
objChart.SetSourceData Source:=pt.TableRange1
Dim LastCol As Long
LastCol = wsTarget.Cells(2, Columns.Count).End(xlToLeft).Column
With wsTarget.Shapes("Chart 1")
.Left = Range("A" & Range("A" & Rows.Count).End(xlUp).Offset(1).Row).Left
.Top = Range("A" & Range("A" & Rows.Count).End(xlUp).Offset(1).Row).Top
End With
Done:
Exit Sub
Errorcatch:
MsgBox Err.Description
Stop
End Sub
I hope someone can help.