[RESOLVED] [2008] 2007 Excel Charting problems

[gigemboy]

I've been racking my brain to try to figure out why my automatically generated charts aren't wanting to work. I am using VS2008, using the Excel 2007 Workbook template (VSTO stuff), and trying to dynamically chart a range of values on one of my sheets. This is to automate approximately 30-40 charts based on the data in the first sheet (dual axis, temp on primary axis, precipitation on secondary axis). The first chart graphs fine using the below code in a button click in the first sheet. The subsequent charts essentially stick another two series on top of the previous chart's series, so instead of two series, it has four. The third chart has six series, and so on. Each chart should only contain two series. Why this is happening, I do not know. This is why I am here Any ideas?

(didn't want to put this in Office Automation because a lot of the answers I get are using vb6 or vba automation code)

'4 to 6 just refers to the two test chart rows I am using, row numbers
For I As Integer = 4 To 6 Step 2
            Dim MyTitle As String = Globals.Sheet1.Range("A" & I.ToString).Value & " - " & _
                                    Globals.Sheet1.Range("B" & I.ToString).Value
            Dim MySubCaption As String = "30 Year Average - " & _
                                         Globals.Sheet1.Range("D" & I.ToString).Value & "°C, " & _
                                         Globals.Sheet1.Range("D" & (I + 1).ToString).Value & " mm"
            Dim MyChart As Excel.Chart = Globals.ThisWorkbook.Charts.Add(System.Type.Missing, Globals.ThisWorkbook.Sheets("Sheet1"), System.Type.Missing, System.Type.Missing)
            MyChart.ChartType = Excel.XlChartType.xlLine
            MyChart.HasTitle = True
            MyChart.ChartTitle.Characters.Font.Size = "10"
            MyChart.ChartTitle.Characters.Text = MyTitle
            MyChart.ChartTitle.Caption = MyTitle & Convert.ToChar(vbLf) & "Climate Diagram" & Convert.ToChar(vbLf) & MySubCaption
            MyChart.ChartArea.Height = "460"
            MyChart.ChartArea.Width = "430"
            MyChart.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLetter
            MyChart.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            '***********************************
            'here is the series code
            Dim PrecipRange As Excel.Range = Globals.Sheet1.Range("E" & (I + 1).ToString, "P" & (I + 1).ToString)
            Dim XLabelRange As Excel.Range = Globals.Sheet1.Range("E3", "P3")
            Dim TempRange As Excel.Range = Globals.Sheet1.Range("E" & I.ToString, "P" & I.ToString)
            Dim MySeriesColl As Excel.SeriesCollection = MyChart.SeriesCollection
            Dim TempSeries As Excel.Series = MySeriesColl.Add(TempRange)
            TempSeries.Name = "Temperature"
            TempSeries.AxisGroup = Excel.XlAxisGroup.xlPrimary
            TempSeries.MarkerSize = "4"
            MySeriesColl = MyChart.SeriesCollection 'required or else second series overwrites first one?
            Dim PrecipSeries As Excel.Series = MySeriesColl.Add(PrecipRange)
            PrecipSeries.Name = "Precipitation"
            PrecipSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary
            PrecipSeries.MarkerSize = "4"
            PrecipSeries.HasLeaderLines = True
            MySeriesColl = MyChart.SeriesCollection 'required or else second series overwrites first one?
            'end series code
            '************************************
            For Each MySeries As Excel.Series In MySeriesColl
                MySeries.Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSolid
                MySeries.Format.Line.Weight = "1"
                MySeries.Format.Line.Style = Microsoft.Office.Core.MsoLineStyle.msoLineSingle
                MySeries.Format.Line.ForeColor.RGB = RGB(255, 127, 0)
                MySeries.MarkerBackgroundColor = Drawing.Color.Black.ToArgb
                MySeries.MarkerForegroundColor = Drawing.Color.Black.ToArgb
            Next
            'first y axis properties
            Dim PrimaryAxis As Excel.Axis = MyChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)
            PrimaryAxis.MaximumScale = 90
            PrimaryAxis.HasTitle = True
            PrimaryAxis.AxisTitle.Characters.Text = "Temperature (°C)"
            PrimaryAxis.AxisTitle.Characters.Font.Size = "10"
            PrimaryAxis.CategoryNames = XLabelRange
            ''second y axis properties
            Dim SecondaryAxis As Excel.Axis = MyChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary)
            SecondaryAxis.MaximumScale = 180
            SecondaryAxis.HasTitle = True
            SecondaryAxis.AxisTitle.Characters.Text = "Precipitation (mm)"
            SecondaryAxis.AxisTitle.Characters.Font.Size = "10"
            'legend properties
            MyChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom
        Next