none
Adding multiple serries to a chart. RRS feed

  • Question

  • So im having trouble adding a second (out of ten) series to a chart in VSTO

    The first series is added fine, but when I add the second, I get a COM exception.  Ive tried using the Excel.Seriescollection namespace, but that draws the same error.

     Private Sub drawRep2Chart()

            Dim repChart2 As Microsoft.Office.Tools.Excel.Chart = Me.Controls.AddChart(Me.Range("B2", "z45"), "chart 1")
            repChart2.ChartType = Excel.XlChartType.xlXYScatterSmooth

            Dim st1C, st2C, st3C, st4C, st5C, st6C, st7C, st8C, st9C, st10C As Integer 'declare series record counters
            st1C = CInt(Range("aw4").Text)
            st2C = CInt(Range("aw5").Text)
            st3C = CInt(Range("aw6").Text)
            st4C = CInt(Range("aw7").Text)
            st5C = CInt(Range("aw8").Text)
            st6C = CInt(Range("aw9").Text)
            st7C = CInt(Range("aw10").Text)
            st8C = CInt(Range("aw11").Text)
            st9C = CInt(Range("aw12").Text)
            st10C = CInt(Range("aw13").Text)

            repChart2.SetSourceData(Me.Range("aa4", "at94"))

            If st1C > 0 Then
                Dim seriesA As Excel.Series = CType(repChart2.SeriesCollection(1), Excel.Series)
                Dim s1End As Integer = CInt(Range("ax4").Text)
                Dim s1y As String = "aa4:aa" & CStr(s1End)
                Dim s1x As String = "ab4:ab" & CStr(s1End)
                seriesA.XValues = Me.Range(s1x)
                seriesA.Values = Me.Range(s1y)
                seriesA.Name = CStr(Me.Range("ab2").Text)

            End If

            If st2C > 0 Then
                Dim seriesB As Excel.Series = CType(repChart2.SeriesCollection(2), Excel.Series)
                Dim s2End As Integer = CInt(Range("ax5").Text)
                Dim s2y As String = "ac4:ac" & CStr(s2End)
                Dim s2x As String = "ad4:ad" & CStr(s2End)
                seriesB.Name = CStr(Me.Range("ad2").Text)
                seriesB.XValues = Me.Range(s2x)
                seriesB.Values = Me.Range(s2y)
            End If



        End Sub

    Friday, September 26, 2014 7:23 AM

Answers

  • Hi LuckyPhil78,

    The reason of that error is that that chart just has only a series, so it will throw the error if you try to access the second series.

    Based on the data that in excel, we can find that the data are incomplete, this lead the chart only has a series.

    To deal with that issue, you need to add series to the chart and set the data for each series.

    For example:

    Dim d As Excel.SeriesCollection = repChart2.SeriesCollection
    Dim newSeries As Excel.Series = d.NewSeries()
    Dim s1End As Integer = CInt(Range("ax4").Text)
    Dim s1y As String = "aa4:aa" & CStr(s1End)
    Dim s1x As String = "ab4:ab" & CStr(s1End)
    newSeries.XValues = Me.Range(s1x)
    newSeries.Values = Me.Range(s1y)
    newSeries.Name = CStr(Me.Range("ab2").Text)

    Since the first series’ data is incorrect, you need to modify its data.

    Best Regards

    Starain Chen


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by LuckyPhil78 Wednesday, October 8, 2014 5:30 AM
    Monday, October 6, 2014 3:26 AM
    Moderator

All replies

  • What is the COM exception you're getting ?

    "If there's nothing wrong with me, maybe there's something wrong with the universe!"

    Friday, September 26, 2014 8:23 AM
  • An exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.Office.Tools.Excel.Implementation.dll but was not handled in user code.
    Monday, September 29, 2014 5:06 AM
  • Hi LuckyPhil78,

    Based on your code, I made a test, it is working. Please check the details of that exception and post it here.

    You may share the project on the OneDrive, we will check it.

    There is a link about excel chart that may benefit you:

    # Create an Excel chart in C# without worksheet data

    http://clear-lines.com/blog/post/Create-an-Excel-chart-in-C-without-worksheet-data.aspx

    Best Regards

    Starain Chen


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 29, 2014 7:14 AM
    Moderator
  • Thanks Starian.

    How do I share the project on OneDrive?

    Monday, September 29, 2014 11:42 AM
  • Hi LuckyPhil78,

    Please refer to:

    # Share files and folders and change permissions

    http://windows.microsoft.com/en-us/onedrive/share-file-folder

    Best Regards

    Starain Chen


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 30, 2014 1:14 AM
    Moderator
  • https://onedrive.live.com/redir?resid=B9151D5C7BC8FEDE!19554&authkey=!ABdbqauxlOL1yfk&ithint=folder%2csuo

    Wednesday, October 1, 2014 7:22 AM
  • Hi LuckyPhil78,

    The reason of that error is that that chart just has only a series, so it will throw the error if you try to access the second series.

    Based on the data that in excel, we can find that the data are incomplete, this lead the chart only has a series.

    To deal with that issue, you need to add series to the chart and set the data for each series.

    For example:

    Dim d As Excel.SeriesCollection = repChart2.SeriesCollection
    Dim newSeries As Excel.Series = d.NewSeries()
    Dim s1End As Integer = CInt(Range("ax4").Text)
    Dim s1y As String = "aa4:aa" & CStr(s1End)
    Dim s1x As String = "ab4:ab" & CStr(s1End)
    newSeries.XValues = Me.Range(s1x)
    newSeries.Values = Me.Range(s1y)
    newSeries.Name = CStr(Me.Range("ab2").Text)

    Since the first series’ data is incorrect, you need to modify its data.

    Best Regards

    Starain Chen


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by LuckyPhil78 Wednesday, October 8, 2014 5:30 AM
    Monday, October 6, 2014 3:26 AM
    Moderator