Create pivot chart from pivot table (XL2007)

[AOB]

Hi guys,

Trying to automate the generation of a pivot chart using VBA

Have managed to create the pivot table fine - all fields set up (pages, rows, columns, data) and it looks fine. I now want to create a pivot chart based on this pivot table.

I'm running into trouble when I try to set the source data for the chart :

Dim objWorksheetPivotTable As Excel.Worksheet
Dim objPivotCache As Excel.PivotCache
Dim objPivotTable As Excel.PivotTable
Dim objPivotChart As Variant
....
With objWorksheetPivotTable

    Set objPivotChart = .Shapes.AddChart

    With objPivotChart
        [COLOR=#ff0000].SetSourceData objPivotTable.TableRange1[/COLOR]
        .ChartType = xlColumnClustered
        .Location xlLocationAsNewSheet, "Pivot Chart"
    End With

End With

I get the following error :

Error 438 : Object doesn't support this property or method

I've tried defining objPivotChart a number of ways (as a Chart, as a Shape - I'm just using Variant now so I can let Excel assign it to suit)

How do I create my pivot chart, based on the existing pivot table?

Thanks

Al

 

[RoryA]

Shapes.Addchart actually returns a Shape, not a Chart, so you need:

 Set objPivotChart = .Shapes.AddChart.Chart

 

[AOB]

Thanks Rory...

 

[RoryA]

Glad to help.

 

[AOB]

Sorry Rory, another question

Trying to format each series by name - setting to a clustered column type, then specify the colour, linestyle etc.

How does one alter the lines (borders?) of the columns and specify the style, weight, color etc.? Similarly for the interior / fill? Can't get anything from intellisense!

 

[RoryA]

For example:

    With ActiveChart.SeriesCollection(3)
        With .Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent3
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0.400000006
            .Transparency = 0
            .Solid
        End With
        With .Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent3
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Weight = 2
        End With
    End With

 

[AOB]

Can the line / fill colours not be set using RGB notation?

It's not liking this :

With .Format
  With .Line
    .Visible = True
    [COLOR=#ff0000].ForeColor.RGB = RGB(69, 98, 135)[/COLOR]
  End With
  With .Fill
    [COLOR=#ff0000].ForeColor.RGB = RGB(92, 131, 180)[/COLOR]
    .Solid
  End With
End With

Error -2147417851 : Automation error
The server threw an exception

 

[RoryA]

Works fine for me in 2010. What are you using?

 

[AOB]

2007

Actually, it seems to be a deeper problem - if I skip past this piece and on to playing with the data labels, I get the same error. Even though, when I set a watch to the Series object, I can see those properties present (so the syntax appears correct)

FYI - I am not running this directly from Excel, this code resides in Access and I am running it through an Excel.Application object. Just to clarify - far as I am aware, this shouldn't be a problem?...

With objWorksheetPivotTable
  Set objPivotChart = .Shapes.AddChart.Chart
  With objPivotChart
    .SetSourceData objPivotTable.TableRange1
    .ChartType = xlColumnClustered
    .Location xlLocationAsNewSheet, "Pivot Chart"
    For Each objSeries In .SeriesCollection
      With objSeries
        Select Case .Name
          Case "Volumes"
            .AxisGroup = 1
            .ChartType = xlColumnClustered
            With .Format
              With .Line
                .Visible = True
[COLOR=#ff0000]                .ForeColor.RGB = RGB(144, 60, 57)[/COLOR]
              End With
              With .Fill
[COLOR=#ff0000]                .ForeColor.RGB = RGB(192, 80, 77)[/COLOR]
                .Solid
              End With
            End With
[COLOR=#ff0000]            .HasDataLabels = True[/COLOR]
            With .DataLabels
              .Alignment = 90
              .Position = xlLabelPositionOutsideEnd
              With .Font
                .Bold = True
                .Size = 8
                .Color = RGB(144, 60, 57)
              End With
            End With
            ....

 

[RoryA]

It shouldn't be a problem, but Excel 2007 is buggy as hell especially where its new charting engine is concerned. Is the application/workbook visible while the code is running?