| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
| Pasting worksheet with embedded charts | Charts and Charting in Excel | |||
| selecting multiple sheets | Excel Worksheet Functions | |||
| Selecting lots of charts | Excel Discussion (Misc queries) | |||
| Pasting excel sheets with charts | Charts and Charting in Excel | |||
Linear Mode
