locked
How to change the Excel Chart Data source? RRS feed

  • Question

  • Scenario:

    In Excel 2007, the user makes a copy of a worksheet (call it Sheet1) containing a  pivot table and pivot chart.  On the new copy sheet (call it Sheet2), the pivot chart remains connected to the pivot table on Sheet1--this is a well known problem in Excel2007 and was fixed on Excel2010.  I'm trying to make a VSTO Excel Application Addin that reconnects the pivot chart on Sheet2 to the pivot table on Sheet2 for Excel2007 users.  However, an exception occurs when calling Chart.SetSourceData.  Example code excerpt below:

     

    Excel.PivotTables pivotTables1 = Excel.PivotTables)VstoWorksheet.PivotTables();
    Excel.PivotTable piv = pivotTables1.Item(1);
    Excel.Range pivData = (Excel.Range)piv.TableRange1;
    Excel.Chart chart1;
    Excel.ChartObjects co = (Excel.ChartObjects)VstoWorksheet.ChartObjects();
    
    if (VstoWorksheet.ChartObjects().Count > 0){
      chart1 = co.Item(1).Chart;
      Excel.Range pivData = (Excel.Range)piv.TableRange1;
      // next statement yields an exception:
     // Error HRESULT E_FAIL has been returned from a call to a COM component.
      chart.SetSourceData(pivData);
    }
    
    

    In the above, VstoWorksheet is of type: Microsoft.Office.Tools.Excel.Worksheet.  I have tried using a Microsoft.Office.Interop.Excel.Worksheet object instead (with appropriate casting adjustments to code), but same exception occurs.

    I have tried other means of generating the excel range in the SetSourceData call, but always I get this exception.  I also tried first deleting all the series in the chart before making the SetSourceData call, but that causes a different exception.

    foreach (Excel.Series ser in chart1.SeriesCollection()) {
      // next statement yields Exception from HRESULT: 0x800A03EC
      ser.Delete();
    }
    Is it possible to do what I'm trying to do here by this or other means?  If not, is this documented somewhere?

    Tools:

    Visual Studio 2010 Pro, using Application Add-in template for Excel 2007.

     

    Thanks,

    pat

    Friday, March 4, 2011 8:50 PM

Answers

  • I can't figure out a way to change the chart's linkage. But since you're automating it anyway, here's a procedure that ought to work:

    Make your pivot table and pivot chart.

    Copy the sheet with pivot table and chart to a new workbook. The pivot chart now is a normal chart, not linked to any range. Instead its values are hard coded into the series formula.

    Move the copied sheet back into the first workbook. The chart is still a regular chart, unlinked to any worksheet data.

    Use the .SetSourceData method of the unlinked chart, and set the source to a cell within the pivot table.

    Ta-da! The copied chart is now linked to the copied pivot table.

     


    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Monday, March 7, 2011 3:39 AM

All replies

  • I can't figure out a way to change the chart's linkage. But since you're automating it anyway, here's a procedure that ought to work:

    Make your pivot table and pivot chart.

    Copy the sheet with pivot table and chart to a new workbook. The pivot chart now is a normal chart, not linked to any range. Instead its values are hard coded into the series formula.

    Move the copied sheet back into the first workbook. The chart is still a regular chart, unlinked to any worksheet data.

    Use the .SetSourceData method of the unlinked chart, and set the source to a cell within the pivot table.

    Ta-da! The copied chart is now linked to the copied pivot table.

     


    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Monday, March 7, 2011 3:39 AM
  • Excel seems to be picky.  You have to use .SetSourceData like:

    chtLastChart.SetSourceData Source:=pvtLastPivot.TableRange1

    Cannot use it in this manner

    chtLastChart.SetSourceData(pvtLastPivot.TableRange1)

     

    By the way here is a macro that will automatically clone Pivot Tables and Charts to spot on the same worksheet by automatically copying to a new workbook copying back and then closing the temporary new workbook, and then going further and automatically linking your new table too.

     

    Sub PivotTableChartClone()
    '
    ' PivotChartCopy Macro
    ' Copy entire worksheet over to new workbook to delink Pivot Chart from original Pivot Table
    ' Takes selected region, and copies any pivot chart or table in that region and returns it back to the original worksheet.
    ' Makes you select region to copy to in original worksheet.
    ' Automatically relinks the new Pivot Chart to the new Pivot Table
       
        Dim rngCopy As Range, rngPaste As Range, wkbCurrent As Workbook, wksCurrent As Worksheet, wkbNew As Workbook
          
        Set rngCopy = Selection
     
        On Error Resume Next
        Set rngPaste = Application.InputBox("Select Cell to Paste Cloned Pivot Table and Chart to", "Clone Pivot", , , , , , 8)
        If rngPaste Is Nothing Then End
        If WorksheetFunction.CountA(rngPaste(1, 1).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count)) > 0 Then
            rngPaste(1, 1).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Select
            MsgBox "Paste region is not clear.  Please make room and try again", , "Error"
            End
        End If
       
        'Paste Column widths first
        rngCopy.Copy
        rngPaste.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
       
        Set wkbCurrent = ActiveWorkbook
        Set wksCurrent = ActiveSheet
       
        'Copy entire worksheet to new workbook.
        wksCurrent.Select
        wksCurrent.Copy
        Set wkbNew = ActiveWorkbook
        Range(rngCopy.Address).Copy
       
        wkbCurrent.Activate
        rngPaste.Select
        ActiveSheet.Paste
       
        Dim chtLastChart As Chart, pvtLastPivot As PivotTable, rngSource As Range
        Set pvtLastPivot = wksCurrent.PivotTables(1) 'For some reason the first index is that last pivot that was added.
       
        Set chtLastChart = wksCurrent.ChartObjects(ActiveSheet.ChartObjects.Count).Chart
        chtLastChart.SetSourceData Source:=pvtLastPivot.TableRange1
       
        wkbNew.Activate
        Application.DisplayAlerts = False
        ActiveWindow.Close
        Application.DisplayAlerts = True
       
    End Sub

    Saturday, April 2, 2011 5:39 AM
  • Excel's not picky, you just need to use the proper VBA syntax. Either of these are fine:

    chtLastChart.SetSourceData Source:=pvtLastPivot.TableRange1

    chtLastChart.SetSourceData pvtLastPivot.TableRange1

    This might also work (note the space before the open parenthesis), but you're never sure how VBA will interpret an argument standing alone with parentheses:

    chtLastChart.SetSourceData (pvtLastPivot.TableRange1)

    Without the space between SetSourceData and the parentheses, you're telling VBA you think SetSourceData is a function (it's a method), and VBA disagrees.


    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Saturday, April 2, 2011 2:48 PM