VSTO/VS2008 Excel Chart issue

[gigemboy]

Automating the generation of a chart through Excel 2007 using VS 2008 and VSTO, and I ran into a "referencing" issue (don't know what else to call it). The chart is just graphing two series on seperate axes (temp on primary, precip on secondary). I am referencing the chart object's seriescollection, and adding two different series to it. However, the second series always seems to overwrite the first one, regardless of the parameters that are passed into the Series.Add method, so it only displays the last series added. I 'solved' this by setting the 'MySeriesColl' variable back to the same object after the first series is added and that fixed the problem (see the commented line of code below), but why is this necessary? Is this normal behavior? The variable was assigned to the same object at the beginning...

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
        Dim TempRange As Excel.Range = Globals.Sheet1.Range("E4", "P4")
        Dim PrecipRange As Excel.Range = Globals.Sheet1.Range("E5", "P5")
        Dim MySeriesColl As Excel.SeriesCollection = MyChart.SeriesCollection
        Dim TempSeries As Excel.Series = MySeriesColl.Add(TempRange, Excel.XlRowCol.xlRows, False, False, False)
        TempSeries.Name = "Temperature"
        TempSeries.AxisGroup = Excel.XlAxisGroup.xlPrimary
        '*******
        MySeriesColl = MyChart.SeriesCollection 'required or else second series overwrites first one?
        '*******
        Dim PrecipSeries As Excel.Series = MySeriesColl.Add(PrecipRange, Excel.XlRowCol.xlRows, False, False, False)
        PrecipSeries.Name = "Precipitation"
        PrecipSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary

[Ecniv]

        Dim MyChart As Excel.Chart
        Dim TempRange As Excel.Range
        Dim PrecipRange As Excel.Range 
        Dim MySeriesColl As Excel.SeriesCollection 
        Dim TempSeries As Excel.Series 
        Dim PrecipSeries As Excel.Series 

        set MyChart = Globals.ThisWorkbook.Charts.Add(System.Type.Missing, Globals.ThisWorkbook.Sheets("Sheet1"), System.Type.Missing, System.Type.Missing)

        MyChart.ChartType = Excel.XlChartType.xlLine

        set TempRange = Globals.Sheet1.Range("E4", "P4")
        set PrecipRange = Globals.Sheet1.Range("E5", "P5")
        setMySeriesColl = MyChart.SeriesCollection
        set tempSeries = MySeriesColl.Add(TempRange, Excel.XlRowCol.xlRows, False, False, False)
        TempSeries.Name = "Temperature"
        TempSeries.AxisGroup = Excel.XlAxisGroup.xlPrimary
        '*******
'        MySeriesColl = MyChart.SeriesCollection 
'required or else second series overwrites first one?
         myserirescoll.refresh '? <-- or requery? perhaps the collection isn't refreshing?
        '*******
        set PrecipSeries = MySeriesColl.Add(PrecipRange, Excel.XlRowCol.xlRows, False, False, False)
        PrecipSeries.Name = "Precipitation"
        PrecipSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary

** Code may be wrong but isn't this a better way of seting the variables? (your choice of course)
Refresh collection is from memory and I haven't checked if that exists... but there is a vague memory of using it.