Selecting Embedded charts in Active Sheets programmatically
 

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?

Selecting Embedded charts in Active Sheets programmatically
 

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 wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Selecting Embedded charts in Active Sheets programmatically
 

THanks. That gets me what I want.
"Andy Pope" wrote in message
...
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 wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Selecting Embedded charts in Active Sheets programmatically
 

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

"Andy Pope" wrote in message
...
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 wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Selecting Embedded charts in Active Sheets programmatically
 

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/
_______


"Barb Reinhardt" wrote in message
...
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

"Andy Pope" wrote in message
...
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 wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Selecting Embedded charts in Active Sheets programmatically
 

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

"Jon Peltier" wrote in message
...
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/
_______


"Barb Reinhardt" wrote in message
...
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

"Andy Pope" wrote in message
...
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 wrote:
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, Microsoft MVP - Excel
http://www.andypope.info