none
Multi level axis in Powerpoint Chart RRS feed

  • Question

  • I'm creating a VSTO add-in that among other things is supposed to create a line chart for some annual data. This data contains datapoints on a weekly basis. I would like the horizontal axis to be grouped in months. This link llustrates the grouping: https://www.pdastats.com/news/archives/750

    I can't however find anything in the VSTO documentations about whether this is possible or not. As far as I can tell a Series only takes a 1-dimensional array of values for the X-axis. Does anyone have any experience with this?

    Saturday, September 30, 2017 12:25 AM

All replies

  • Hello,

    Here is the VBA code:

    Sub CreateChart()
    Dim myChart As Chart
    Dim gChartData As ChartData
    Dim gWorkBook As Excel.Workbook
    Dim gWorkSheet As Excel.Worksheet
    ' Create the chart and set a reference to the chart data.
    Set myChart = ActivePresentation.Slides(1).Shapes.AddChart.Chart
    myChart.ChartType = xlLine
    Set gChartData = myChart.ChartData
    ' Set the Workbook and Worksheet references.
    Set gWorkBook = gChartData.Workbook
    Set gWorkSheet = gWorkBook.Worksheets(1)
    ' Add the data to the workbook.
    gWorkSheet.ListObjects("Table1").Resize gWorkSheet.Range("A1:C13")
    gWorkSheet.Range("Table1[[#Headers],[Series 1]]").Value = "X"
    gWorkSheet.Range("A2").Value = "2015"
    gWorkSheet.Range("A3:A7").ClearContents
    gWorkSheet.Range("A8").Value = "2016"
    gWorkSheet.Range("A9:A13").ClearContents
    gWorkSheet.Range("B2").Value = "7/15/2015"
    gWorkSheet.Range("B3").Value = "8/15/2015"
    gWorkSheet.Range("B2:B3").AutoFill Destination:=gWorkSheet.Range("B2:B13")
    gWorkSheet.Range("B2:B13").NumberFormat = "d-mmm"
    gWorkSheet.Range("Table1[[#Headers],[Series 2]]").Value = "Y"
    gWorkSheet.Range("C2").Value = "100"
    gWorkSheet.Range("C3").Value = "150"
    gWorkSheet.Range("C2:C3").AutoFill Destination:=gWorkSheet.Range("C2:C13")
    myChart.SeriesCollection(1).Delete
    myChart.FullSeriesCollection(1).Values = "=Sheet1!$C$2:$C$13"
    myChart.FullSeriesCollection(1).XValues = "=Sheet1!$A$2:$B$13"
    ' Clean up the references.
    Set gWorkSheet = Nothing
    ' gWorkBook.Application.Quit
    Set gWorkBook = Nothing
    Set gChartData = Nothing
    Set myChart = Nothing
    End Sub
    
    

    In VSTO add-ins using C#, you could use:

                PowerPoint.Slide slide = Globals.ThisAddIn.Application.ActivePresentation.Slides.Add(Globals.ThisAddIn.Application.ActivePresentation.Slides.Count + 1, PowerPoint.PpSlideLayout.ppLayoutBlank);
                PowerPoint.Chart chart = slide.Shapes.AddChart(XlChartType.xlLine, 66, 113, 828, 384).Chart;
                Excel.Workbook wb = chart.ChartData.Workbook;
                Excel.Worksheet ws = wb.Worksheets[1];
                ws.ListObjects["Table1"].Resize(ws.Range["A1:C13"]);
                ws.Range["Table1[[#Headers],[Series 1]]"].Value = "X";
                ws.Range["A2"].Value = "2015";
                ws.Range["A3:A7"].ClearContents();
                ws.Range["A8"].Value = "2016";
                ws.Range["A9:A13"].ClearContents();
                ws.Range["B2"].Value = "7/15/2015";
                ws.Range["B3"].Value = "8/15/2015";
                ws.Range["B2:B3"].AutoFill(Destination: ws.Range["B2:B13"]);
                ws.Range["B2:B13"].NumberFormat = "d-mmm";
                ws.Range["Table1[[#Headers],[Series 2]]"].Value = "Y";
                ws.Range["C2"].Value = "100";
                ws.Range["C3"].Value = "150";
                ws.Range["C2:C3"].AutoFill(Destination: ws.Range["C2:C13"]);
                chart.SeriesCollection(1).Delete();
                chart.FullSeriesCollection(1).Values = "=Sheet1!$C$2:$C$13";
                chart.FullSeriesCollection(1).XValues = "=Sheet1!$A$2:$B$13";

    The chart:

    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.

    Monday, October 2, 2017 7:14 AM
    Moderator