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

 

[AOB]

Yes, it is, although once finished I would probably prefer to hide it until all the work has been done and then show the finished chart.

I use VBA in Access to export data to Excel and work it into charts all the time, and never come across this problem before?

 

[RoryA]

Just to test, does it make any difference if you explicitly select the item you want to manipulate before you manipulate it?

 

[AOB]

Do you mean :

For Each objSeries In .SeriesCollection
  With objSeries
    [COLOR=#ff0000].Select[/COLOR]
    Select Case .Name
      Case "Volumes"
      ...

'Cos guess what - that throws an automation error too!

Error -2147467259 : Automation Error
Unspecified error

 

[AOB]

I notice as well that, again setting a watch on the Series object, but also keeping an eye on the actual chart in Excel, that while certain properties are updated in the watch window, the chart stays the same.

Namely (with a different series), where I switch the AxisGroup to 2 (the secondary axis) and change the ChartType to Line With Markers - the property value changes in the watch window, but the chart in Excel appears exactly the same (e.g. in this example, the series remains a clustered column, plotted on the primary axis)

It's really bizarre behaviour

Could it be anything to do with the fact that I have set the source data range to be the pivot table (and not the source data for the pivot table?)