[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

[Tom Sawyer]

I haven't done any Excel programming for a couple of years so things may have gotten better, but I recall it as having a great number of memory leaks, especially when you tried to do the graphing stuff.

It may be that the reference to the series collection isn't getting reset each time through the loop like it would with every other object and Excel is holding onto the reference, so when you add new series to it, it puts them ontop of the ones that are already there.

Try a MySeriesColl.Clear (or whatever the exact syntax would be for it) just after you instantiate it. That way, if it's holding onto a reference from a previous loop, you'll get rid of the series that are inside of it. That shouldn't work, but Excel does a lot of weird stuff.

[gigemboy]

Thats just the thing. There is no "clear" method. The only thing I see is an Add method and a Paste method. Working with the Paste method right now but still can't get it to work grrrr...

http://msdn2.microsoft.com/en-us/library/bb178382.aspx

[gigemboy]

any way I try I still cannot "reset" the SeriesCollection object. Even trying to replace it by index numbers throws an error... Its not anything to do with the loop. If I programmatically generate one chart (sub finishes) and add another chart right after using the same sub, then two extra series are still included in the second chart.

[Tom Sawyer]

Maybe try a MyChart.SeriesCollection = new Excel.SeriesCollection just before you declare the MySeriesColl. By the sounds of the problem, you just need to kick Excel and tell it to let go of the old reference each time through the loop.

[Tom Sawyer]

If that doesn't work, try putting this before assigning to MySeriesColl:

Dim iSeriesCnt as Integer = MyChart.SeriesCollection.Count

For i as Integer = iSeriesCnt To 0 Step -1
    MyChart.SeriesCollection.Item(i).Delete
Next

[gigemboy]

Can set the series collection to a new series collection because it (vsto) doesn't allow you to instantiate a new seriescollection object. Every time I try the SeriesCollection.Item(index).Delete() method, it throws an "Invalid Parameter" error. I've actually tried these methods before, and I just cannot seem to be able to do it (*banging keyboard keys* )

[gigemboy]

Apparently all of these headaches came around because I did not know that index numbers start at 1 instead of 0. The below code worked to remove the items from the seriescollection...

                MySeriesColl.Item(2).Delete()
                MySeriesColl.Item(1).Delete()
                MySeriesColl.NewSeries()
                MySeriesColl = MyChart.SeriesCollection

Deletes the two series items, in reverse order, then creates a new series. The .NewSeries call was required even though later in the code it uses the SeriesCollection.Add method to add a series in it. If the .NewSeries call isn't there, the .Add method bombs out. And even if you have all of that, if you don't RESET the MySeriesColl variable BACK to the .SeriesCollection object (end of the code), the rest of the code would bomb out as well.

Is office automation always this screwy?? Is there any documentation out there that explains why all of this is necessary (redundant variable assigning, redundant function calls, etc)?

[Tom Sawyer]

That's messed up.

Office Automation does tend to be screwy like that. The reason is because generally with VB, all the objects and their references are handled by the VB program. When you add in Office, you create an application that handles the objects and references that are assigned to it on its own.

The two things are not always in sync, so you have cases like this where VB releases the reference to the object and thinks it's starting over, while Office still has the old reference and thinks you're adding onto what you did before. It's a bit goofy.