Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Create pivot chart from pivot table (XL2007)

  1. #1
    Board Regular AOB's Avatar
    Join Date
    Dec 2010
    Location
    Dublin, Ireland
    Posts
    596
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create pivot chart from pivot table (XL2007)

    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 :

    Code:
    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
            .SetSourceData objPivotTable.TableRange1
            .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

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)

    Shapes.Addchart actually returns a Shape, not a Chart, so you need:
    Code:
     Set objPivotChart = .Shapes.AddChart.Chart

  3. #3
    Board Regular AOB's Avatar
    Join Date
    Dec 2010
    Location
    Dublin, Ireland
    Posts
    596
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)



    Thanks Rory...

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)


    Glad to help.

  5. #5
    Board Regular AOB's Avatar
    Join Date
    Dec 2010
    Location
    Dublin, Ireland
    Posts
    596
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)

    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!

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)

    For example:
    Code:
        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

  7. #7
    Board Regular AOB's Avatar
    Join Date
    Dec 2010
    Location
    Dublin, Ireland
    Posts
    596
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)

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

    It's not liking this :

    Code:
    With .Format
      With .Line
        .Visible = True
        .ForeColor.RGB = RGB(69, 98, 135)
      End With
      With .Fill
        .ForeColor.RGB = RGB(92, 131, 180)
        .Solid
      End With
    End With
    Error -2147417851 : Automation error
    The server threw an exception

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)

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

  9. #9
    Board Regular AOB's Avatar
    Join Date
    Dec 2010
    Location
    Dublin, Ireland
    Posts
    596
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)

    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?...

    Code:
    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
                    .ForeColor.RGB = RGB(144, 60, 57)
                  End With
                  With .Fill
                    .ForeColor.RGB = RGB(192, 80, 77)
                    .Solid
                  End With
                End With
                .HasDataLabels = True
                With .DataLabels
                  .Alignment = 90
                  .Position = xlLabelPositionOutsideEnd
                  With .Font
                    .Bold = True
                    .Size = 8
                    .Color = RGB(144, 60, 57)
                  End With
                End With
                ....

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Create pivot chart from pivot table (XL2007)

    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?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •