none
Pie Charts with Excel 2010 RRS feed

  • Question

  • I have a project I am doing that requires one file to be a pie chart feeding off of data from another file. Is this possible to have 1 file with a spreadsheet of pie charts and a separate file containing spreadsheet of data? If so how do I create this in Excel 2010? I do not want the users to see the data, that is why I need it to be 2 separate files.
    Thursday, January 5, 2017 8:31 PM

All replies

  • Hi,

    We could set the data source of the chart from another worksheet or workbook.

    You could test with the code below. Change the path and name of your datasource workbook.

    Sub Test1()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim cha As Chart
    Set ws = ActiveSheet
    Set wb = Workbooks.Open("D:\test.xlsx")
    ws.Activate
    ws.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Workbooks("test.xlsx").Sheets("Sheet1").Range("A1:B5")
    wb.Close
    Application.ScreenUpdating = True
    End Sub

    Regards,

    Celeste


     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, January 6, 2017 6:08 AM
    Moderator
  • I believe that the following code was only introduced in Excel 2013 and will error with Excel 2010 that the OP has indicated in the post title.

    ws.Shapes.AddChart2(251, xlPie).Select

    Excel 2010 code as follows:

    ws.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie

    Also without providing the data source to the end user then each time the workbook is opened then the user will get a message re updating external links.


    Regards, OssieMac

    Friday, January 6, 2017 7:04 AM