1

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.

1 Answer 1

1

If objChart is a ChartObject, you need to insert Chart into this line

objChart.SetSourceData Source:=pt.TableRange1

so it reads

objChart.Chart.SetSourceData Source:=pt.TableRange1

The ChartObject is the shape in the worksheet, the Chart is the chart contained within the ChartObject. And it's the Chart that plots the data.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.