Changing Series in XL Chart dynamically

[JBG]

Hi experts,

I am trying to update the 5 series in an existing chart in an XL spreadsheet.

Internally (when I look in the excel's VB editor), the chart is referred to as "Chart2"

Here is the snippet of code that continues to return this error:
"1004 - Unable to set the values Property of the series class"

.....etc

Dim objExcelApp As Object
Dim objExcelSheet As Object
Dim objExcelWorkbook As Excel.Workbook
Dim objChart As New Excel.Chart
Dim objSeries As Excel.Series
Dim objSC As Excel.SeriesCollection

.....etc

Set objExcelApp = CreateObject("Excel.Application")
Set objExcelWorkbook = objExcelApp.Workbooks.Open(g_strXLpath)
    
Set objChart = objExcelWorkbook.Charts(1)
objChart.Activate
For Each objSeries In objChart.SeriesCollection
    objSeries.Values = &quot;=Sheet1!$Q$2:$Q$10&quot; (<-errors here)
Next

What am I doing wrong? The data that is in the spreadsheet resides on sheet1, and the chart, as I said, is referred to has "Chart2." The sheet1 data has 15 rows and 50 cols of data, so I am not referring to an empty range.

I have also tried = "=Chart2!$Q$2:$Q$10" and no go.

Help!

JBG

 

[PHV]

Have you tried this ?
objSeries.Values = Worksheets("Sheet1&quot.Range("Q2:Q10&quot

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JBG]

I will give it a go as soon as I get work.

Much obliged...

Jeff

 

[JBG]

It worked. Thank you VERY much for the reply...

Jeff

 

[SkipVought]

Jeff,

All well and good. However, it does not answer the DYNAMIC range question. objSeries.Values = Worksheets("Sheet1&quot.Range("Q2:Q10&quot is not dymanic if the number of row of data changes.

Check out the FAQ, with particular attention to the OFFSET function.

faq68-1331 How can I rename a table as it changes size

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JBG]

Thanks skip.

Here is how I am doing the code,

First I return the total number of rows from the spreadsheet, say that number is 10 (intNumberOfRows).

Knowing that i am going to write to that spreadheet a new row, meaning that I will now have 11 rows, I use this:

objSeries.Values = Worksheets("data&quot.Range("I2:I" & intNumberOfRows + 1)


So, since I know the cols (which never change), I know the series, which always use the same cols, and I know that I simply adding a row to the data sheet that is used as the source for the charts, my goal is simply to increment the row val of the series, for each series, by one.

BTW, I am automating this functionality from an Access 2000 module. For sake of chatting with all of you experts (amd I mean that, cuz I am at best inept at this stuff), here is the code:

Public Sub s_LoadOutputSpreadsheet()
    
    Dim objExcelApp As Object
    Dim objExcelSheet As excel.Worksheet
    Dim objExcelWorkbook As excel.Workbook
    Dim intRow As Integer
    Dim strString As String
    Dim strSQL As String
    Dim intNumberOfRows As Integer
    Dim intNumberOfCols As Integer
    Dim x As Integer
    Dim y As Integer
    Dim varVal As Variant
    Dim varItem As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim objXLws As excel.Worksheet
    Dim objChart As New excel.Chart
    Dim objSeries As excel.Series
    Dim objSC As excel.SeriesCollection
    Dim varVals As Variant
    Dim fso As Scripting.FileSystemObject

    On Error GoTo errHandler
    Set fso = New Scripting.FileSystemObject
    DoEvents
    SetAttr g_strXLpath, vbNormal
    Set objExcelApp = CreateObject("Excel.Application")
    
    Set objExcelWorkbook = objExcelApp.Workbooks.Open(g_strXLpath)
    intNumberOfRows = f_GetNumberOfRows(intNumberOfCols)
    Set objChart = objExcelWorkbook.Charts(1)
    objChart.Activate
    Set objExcelSheet = objExcelWorkbook.Sheets("data")
    Set dbs = Application.CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblXL")
    If Not rst.EOF Then
        rst.MoveLast
        rst.MoveFirst
        rst.MoveLast
        For x = 0 To rst.Fields.Count - 1
            For y = 1 To intNumberOfCols
                If objExcelSheet.Cells(1, y) = rst.Fields(x).Name Then
                    objExcelSheet.Cells(intNumberOfRows, y) = rst.Fields(x).Value
                End If
            Next
        Next
    End If
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    For Each objSeries In objChart.SeriesCollection
        objSeries.Values = Worksheets("data").Range("I2:I" & intNumberOfRows + 1)
    Next
    
    objExcelApp.Workbooks(1).Save
    objExcelApp.Quit
    Set objExcelSheet = Nothing
    Set objExcelApp = Nothing
    
    Exit Sub
errHandler:
    MsgBox Err.Number & " " & Err.Description
    


End Sub

Unless I am missing something, this is working ok....and if I am doing something stupid or inefficient (likely), let me know.

Again, thank you taking the time to respond. I appreciate it...

Jeff

 

[SkipVought]

It's not a big deal in this case since you already have the code.

    For Each objSeries In objChart.SeriesCollection
        objSeries.Values = Worksheets("data").Range("I2:I" & intNumberOfRows + 1)
    Next

But you do NOT need to have code to do this!

A dynamic named range defined in Insert/Name/Define using the OFFSET function will eliminate any need for code to keep your table dynamic for any number of spreadhseet and coding needs.

Using the OFFSET function, you can define a sliding window -- for instance if your table had 50 rows, but you only wanted to plot 10 row (data points) at a time and use a scroll bar or spinner to move that 10-point window.

REALLY EASY with the OFFSET function! No code necessary in many cases!


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JBG]

very cool. I am going to have my project lead (and an excel guy) look at your response....

Jeff