[RESOLVED] [2005] Determine variables in Excel Chart

[campster]

I hate to keep posting Excel questions but I have another question. I'm trying to open an Excel template that contains two worksheets and one or more charts. I want to be able to take the charts and load the chart name and variables in a treeview. I can get the chart names into the parent nodes but I'm having trouble getting the Variables associated to each chart. I've tried using the seriescollection but something just isn't right.

I guess my question is how can I determine the variables in an Excel charts series?

Below is what I have. The "var =" line is where my troubles begin.

Public Function GetCharts() As TreeNode
        Dim parentNode As TreeNode = Nothing
        Dim var As String = ""
        Dim i as Integer = 1
 
        For Each oCht As Excel.Chart In oExcel.Workbooks(1).Charts
 
            parentNode = New TreeNode(oCht.Name)
            Do
                var = CType(oCht.SeriesCollection, Excel.SeriesCollection).Item(i).Name
                i += 1
                parentNode.Nodes.Add(var)
            Loop While var <> ""
 
        Next
 
        Return parentNode
 
    End Function

[campster]

After thinking about what is avaiable to a chart, I've realized the the variables do not exist directly. I need to access the range of the series then I can go to the worksheet that contains the series data and extract the variable names.

This brings me back to the seriecollection method of the chart object. Does anyone know how to use this to get the range of the Y axis values?

[campster]

I think that I'm getting close. I can get the number of variables per chart using the code below but when I try to run I get an exception when I cast the seriesCollection.item to a string.

Public Function GetCharts() As TreeNode
        Dim parentNode As TreeNode = Nothing
 
        Try
 
            For Each oCht As Excel.Chart In oExcel.Workbooks(1).Charts
                Dim var As String
 
                parentNode = New TreeNode(oCht.Name)
 
                Dim cnt As Integer = CType(oCht.SeriesCollection, Excel.SeriesCollection).Count
                For i As Integer = 1 To cnt
                    var = CStr(CType(oCht.SeriesCollection, Excel.SeriesCollection).Item(i))
                    parentNode.Nodes.Add(var)
                Next
            Next
 
        Catch ex As Exception
            MessageBox.Show("GetCharts.GetVariables: " & ex.Message.ToString)
        End Try
 
        Return parentNode
 
    End Function

[campster]

bump

I'm finally at a point that this is holding up progress. Any Excel/.Net experts out there that want to give this a go?

[RobDog888]

Try adding another level of casting as you are casting the collection but not the item.

var = Ctype(CType(oCht.SeriesCollection, Excel.SeriesCollection).Item(i), WhatEverItIsSupossedToBeType).ToString

[campster]

I think that I'm trying to read data that is not available. I'm trying various versions of the code below and keep getting and error that says:
"Unable to get the Name property of the Series Class".

The property is available in intelisense but throws an exception when I acces it.

var=cstr(ctype(CType(oCht.SeriesCollection, Excel.SeriesCollection).Item(i),Excel.Series).Name)

[campster]

I'm trying to read this data from an Excel Template. I just tried to read the same data from an xls file and although I did not get exactly what I wanted, I was able to see the series name.

So what is the difference in reading the chart format from a template (xlt) versus an xls ?

[campster]

It looks like I'm going to have to find a new way to do what I want. I just tried to access the seriescollection of a template using VBA and I get the same results. For some reason, the data isn't available for template files.

[campster]

HOLY CRAP....

This had nothing to do with xlt versus xls. The series range can not be obtained programatically unless the series has at least one data point. The solution was to put a data point (0) into every column of my template's data sheet, extract the range and then delete the data.