Hi experts,
This is part 2 of my dilemma (and I apologize for the long post). I had previously posted that the solution(s) I was given worked, but they did not.
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 does not error, but does not work either:
This is part 2 of my dilemma (and I apologize for the long post). I had previously posted that the solution(s) I was given worked, but they did not.
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 does not error, but does not work either:
Code:
.....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
Select Case objSeries.Name
Case "nbrSmsAvTotalCompPct"
objSeries.Values = Worksheets("data").Range("I2:I" & intNumberOfRows + 2)
Case "nbrSmsAvDTCompPct"
objSeries.Values = Worksheets("data").Range("M2:M" & intNumberOfRows + 2)
Case "nbrSmsAvLTCompPct"
objSeries.Values = Worksheets("data").Range("Q2:Q" & intNumberOfRows + 2)
Case "nbrSmsAvTotalUnver"
objSeries.Values = Worksheets("data").Range("R2:R" & intNumberOfRows + 2)
Case "nbrSmsAvDTUnver"
objSeries.Values = Worksheets("data").Range("S2:S" & intNumberOfRows + 2)
Case "nbrSmsAvLTUnver"
' 'objSeries.Values = Worksheets("data").Range("T2:T" & intNumberOfRows + 2)
End Select
Next
The deal here is that, when you right click on the chart and go to Source Data, you get a 2-tab window that defines the data ranges for each "series" that the chart utlizes.
Now, the chart uses sheet4/"data" (which is 50 cols wide)as its data source. The chart uses only about 25 of those cols, and each time I add a row to sheet4 (which I can do no problem) I need to adust the data ranges (within the chart) that the chart uses so that it utilizes/recognizes the new row.
For example, the first series, which I will call nbrSmsAvTotalCompPct, uses this as the "Name" (lifted directly from the Source Data 2-tab window, tab 2): =Data!$I$1
and this as the "Values": "=Data!$I$2:$I$10"
and this as the "Xvalues": "=Data!$B$2:$B$10"
What I would like is some sample VBA code (that I will use in an Access module) to be able to update , I think/hope, the "Values" so that the chart will now use/recognize the new row that has been added.
So, I want "=Data!$I$2:$I$10"
to change to
"=Data!$I$2:$I$11"
In a previous reply by Office Solutions.com, he posted a a cool fix used in the spreadsheet, which I couldnt get to work. But I want to be able to program this for several reasons.
So, anyone have some sample code and a quick explanation as to which objects are used ?(I do not even know if the above code is on the right track)
I am snorkling around the solution using setDataSource, ChartWizard, and Series, but cannot get this dang thing to work. I am past the frustrated stage and need a bit more help before I, well, snap.
Again.... HELP!
Thanks,
Jeff
