[RESOLVED] [2005] Select Excel Range from Listbox

[campster]

I have a list box (extended multi-select) that contains a list of possible variables that I can plot in Excel. The listbox contains only the variable names which will be found in the header row of my Excel worksheet. I want to select one or more variables from the listbox and plot the selected data.

I can manually set the range as shown in red below and the plot is generated as I want. The problem that I'm having is trying to set this range based on the listbox selection. I can get a numeric value that represents the column that I want but how do I get the A:A syntax? Do I need this format?

        Dim oCht As Excel.Chart
        Dim oSht As Excel.Worksheet
        Dim oWB As Excel.Workbook
        Dim oRng As Excel.Range
        Dim strRng As String = "A:A" 'The first column (time) will always be used

        Try
            oApp = New Excel.Application
            oWB = oApp.Workbooks.Open("c:\temp\test.xls")
            oSht = CType(oApp.Worksheets(1), Excel.Worksheet)
            oCht = CType(oWB.Charts.Add, Excel.Chart)

            'For Each idx As Integer In lstAllVars.SelectedIndices
            '    strRng = lstAllVars.Items(idx).ToString
            '    oRng = CType(oSht.Cells(1, idx), Excel.Range).EntireColumn
            'Next
            oRng = oSht.Range("A:A,B:B,D:D")

            With oCht
                .SetSourceData(oRng)
                .Name = "MyChartTab"
                .PlotBy = Excel.XlRowCol.xlColumns
                .HasTitle = True
                .ChartTitle.Text = "MyChart"
            End With

            oApp.Visible = True

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try

[campster]

Man this stuff isn't hard if you know where to look. The following code does what I want. I'm sure that there is a better way but this will have to do for now.

        Dim oCht As Excel.Chart
        Dim oSht As Excel.Worksheet
        Dim oWB As Excel.Workbook
        Dim oRng As Excel.Range
        Dim strRng As String = ""
        Dim tempRng As String

        Try
            oApp = New Excel.Application
            oWB = oApp.Workbooks.Open("c:\temp\test.xls")
            oSht = CType(oApp.Worksheets(1), Excel.Worksheet)
            oCht = CType(oWB.Charts.Add, Excel.Chart)

            For Each idx As Integer In lstAllVars.SelectedIndices
                tempRng = CType(oSht.Cells(1, idx + 1), Excel.Range).AddressLocal
                tempRng = tempRng(1) & ":" & tempRng(1)

                If strRng.Length > 0 Then
                    strRng = strRng & "," & tempRng
                Else
                    strRng = tempRng
                End If

            Next

            oRng = oSht.Range(strRng)

            With oCht
                .SetSourceData(oRng)
                .Name = "MyChartTab"
                .PlotBy = Excel.XlRowCol.xlColumns
                .HasTitle = True
                .ChartTitle.Text = "MyChart"
            End With

            oApp.Visible = True

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try

[campster]

I found that the code in my last post workd fine if I did not have more than 255 rows of data. If there were more than 255, I would get an error that I could only have 255 series. This confused me at first because I only had 5 - 6 series with 500 points (rows).

I found the problem here:

With oCht
                .SetSourceData(oRng)
                .Name = "MyChartTab"
                .PlotBy = Excel.XlRowCol.xlColumns
                .HasTitle = True
                .ChartTitle.Text = "MyChart"
            End With

Evidently when you set the source data, it defaults to using the rows as series. Then when you use the plotby property, the series are defined by the columns. There was no problem with small data sets because you could use the rows as series and then just redifine things later. Not the case with larger data sets. The answer is to define the sourcedata and plotby at the same time:

.SetSourceData(oRng, Excel.XlRowCol.xlColumns)