Selecting Embedded charts in Active Sheets programmatically

[Barb Reinhardt]

I have a workbook with about 15 worksheets. On each worksheet is typically
one chart ... unfortunately, the chart name is not always Chart 1. I have
this code.

For Each sht In ActiveWorkbook.Worksheets
sht.Activate
'If sht.ProtectContents = True Then
'sht.Unprotect Password:=""
'End If

'Worksheets(sht).Activate
chartcount = ActiveSheet.ChartObjects.Count
Debug.Print chartcount
If chartcount > 0 Then
For j = 1 To chartcount
ActiveSheet.ChartObjects("Chart " & j).Activate
'Worksheets(sht).ChartObjects(cht).Activate
seriescount = ActiveChart.SeriesCollection.Count
For k = 1 To seriescount
SeriesFormula = ActiveChart.SeriesCollection(k).Formula
Debug.Print sh; j; xval, yval; SeriesFormula
Next k
'Next cht
Next j
End If
'Debug.Print sh; chartcount
Next sht


WHICH works if the chart name starts at chart 1 on the sheet and increments
by one. It doesn't work if the chart name is CHART 9. What do I need to
change to get it to select the chart name that is in the sheet?

 

[Andy Pope]

Hi,

Try the following, which uses the ChartObjects collection similar to the
worksheets collection you have already used.

Sub X()
Dim sht As Worksheet
Dim objCht As ChartObject
Dim xVal, yVal, seriesformula
Dim k, i

For Each sht In ActiveWorkbook.Worksheets
sht.Activate
For Each objCht In sht.ChartObjects
With objCht.Chart
For k = 1 To .SeriesCollection.Count
xVal = .SeriesCollection(k).XValues
yVal = .SeriesCollection(k).Values
seriesformula = .SeriesCollection(k).Formula
Debug.Print sht.Name; " has chart "; .Parent.Name; _
". Series"; k; " formula"; seriesformula
For i = LBound(xVal) To UBound(xVal)
Debug.Print "x("; i + 1; ") ="; xVal(i), _
"y("; i + 1; ") ="; yVal(i)
Next
Next k
End With
Next
Next sht
End Sub

Cheers
Andy

 

[Barb Reinhardt]

THanks. That gets me what I want.

Hi,

Try the following, which uses the ChartObjects collection similar to the
worksheets collection you have already used.

Sub X()
Dim sht As Worksheet
Dim objCht As ChartObject
Dim xVal, yVal, seriesformula
Dim k, i

For Each sht In ActiveWorkbook.Worksheets
sht.Activate
For Each objCht In sht.ChartObjects
With objCht.Chart
For k = 1 To .SeriesCollection.Count
xVal = .SeriesCollection(k).XValues
yVal = .SeriesCollection(k).Values
seriesformula = .SeriesCollection(k).Formula
Debug.Print sht.Name; " has chart "; .Parent.Name; _
". Series"; k; " formula"; seriesformula
For i = LBound(xVal) To UBound(xVal)
Debug.Print "x("; i + 1; ") ="; xVal(i), _
"y("; i + 1; ") ="; yVal(i)
Next
Next k
End With
Next
Next sht
End Sub

Cheers
Andy

 

[Barb Reinhardt]

Andy,

I'm trying to print this info to a worksheet and I have the following

Workbooks(CurBook).Worksheets("SeriesList").Range("a" & lastrow + 1).Value =
sht.Name
Workbooks(CurBook).Worksheets("SeriesList").Range("b" & lastrow + 1).Value =
..Parent.Name
Workbooks(CurBook).Worksheets("SeriesList").Range("c" & lastrow + 1).Value =
k
Workbooks(CurBook).Worksheets("SeriesList").Range("d" & lastrow + 1).Value =
seriesformula

The first three work, but when it gets to the seriesformula line, I get a an
"Application defined or object defined error". Suggestions?

Thanks,
Barb Reinhardt

 

[Jon Peltier]

Barb -

Try this. Without the apostrophe, Excel is trying to interpret the formula.

Workbooks(CurBook).Worksheets("SeriesList").Range("d" & lastrow + 1).Value =
"'" & seriesformula

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Andy,

I'm trying to print this info to a worksheet and I have the following

Workbooks(CurBook).Worksheets("SeriesList").Range("a" & lastrow + 1).Value
= sht.Name
Workbooks(CurBook).Worksheets("SeriesList").Range("b" & lastrow + 1).Value
= .Parent.Name
Workbooks(CurBook).Worksheets("SeriesList").Range("c" & lastrow + 1).Value
= k
Workbooks(CurBook).Worksheets("SeriesList").Range("d" & lastrow + 1).Value
= seriesformula

The first three work, but when it gets to the seriesformula line, I get a
an "Application defined or object defined error". Suggestions?

Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

Jon,

After I posted this, I was wondering if that might be the issue, but didn't
have a chance to check it.

Thanks,
Barb

Barb -

Try this. Without the apostrophe, Excel is trying to interpret the
formula.

Workbooks(CurBook).Worksheets("SeriesList").Range("d" & lastrow + 1).Value
=
"'" & seriesformula

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______