software

I'm wondering if the charting group can assist.

quot;Barb Reinhardtquot; gt; wrote in message
...
gt; If I have a list of worksheets in a workbook, with VBA, how do I determine
gt; the following:
gt;
gt; 1) If there is a chart on the worksheet and the chart identification
gt; 2) If there is a chart ... how do I determine the number of series
gt; displayed in the chart And how do I display the SERIES #, the XVAL and the
gt; YVAL for each series?
gt;
gt; Thanks in advance,
gt; Barb Reinhardt
All you have to do is add the code to write out the information of
interest to you.

Option Explicit
Sub InitSetup(aWB As Workbook, ByRef WKSChart As Worksheet, _
ByRef ChartSeriesData As Worksheet)
'Should actually check if these sheets exist
Set WKSChart = aWB.Worksheets.Add
Set ChartSeriesData = aWB.Worksheets.Add
WKSChart.Name = quot;WKS Chartsquot;
ChartSeriesData.Name = quot;Chart Seriesquot;
'also need to add headers
End Sub
Sub writeChartInfo(ByRef TargCell As Range, _
aChart As Chart)
'write data of interest in row of targcell, then
Set TargCell = TargCell.Offset(1, 0)
End Sub
Sub writeSeriesInfo(ByRef TargCell As Range, _
aChart As Chart)
Dim aSeries As Series
For Each aSeries In aChart.SeriesCollection
'write chart series info to TargCell row, then
Set TargCell = TargCell.Offset(1, 0)
Next aSeries
End Sub
Sub analyzeAllEmbeddedCharts()
Dim aWS As Worksheet, aChartObj As ChartObject, _
WKSChart As Worksheet, ChartSeriesData As Worksheet, _
ChartWKSCell As Range, SeriesWKSCell As Range
InitSetup ActiveWorkbook, WKSChart, ChartSeriesData
Set ChartWKSCell = WKSChart.Cells(2, 1)
Set SeriesWKSCell = ChartSeriesData.Cells(2, 1)
For Each aWS In ActiveWorkbook.Worksheets
For Each aChartObj In aWS.ChartObjects
writeChartInfo ChartWKSCell, aChartObj.Chart
writeSeriesInfo SeriesWKSCell, aChartObj.Chart
Next aChartObj
Next aWS
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article gt;,says...
gt; I'm wondering if the charting group can assist.
gt;
gt; quot;Barb Reinhardtquot; gt; wrote in message
gt; ...
gt; gt; If I have a list of worksheets in a workbook, with VBA, how do I determine
gt; gt; the following:
gt; gt;
gt; gt; 1) If there is a chart on the worksheet and the chart identification
gt; gt; 2) If there is a chart ... how do I determine the number of series
gt; gt; displayed in the chart And how do I display the SERIES #, the XVAL and the
gt; gt; YVAL for each series?
gt; gt;
gt; gt; Thanks in advance,
gt; gt; Barb Reinhardt
gt;
gt;
gt;

Tushar,

That gets me a lot closer. What I want to know is programmatically, how do
I identify the X and Y axis entries for the chart and series of interest and
print them out. What I want is

WORKSHEET NAME CHART NAME SERIES # X VALUES Y VALUES

on a spreadsheet. How do I find out how to do this?

Thanks,
Barb Reinhardt

quot;Tushar Mehtaquot; wrote:

gt; All you have to do is add the code to write out the information of
gt; interest to you.
gt;
gt; Option Explicit
gt; Sub InitSetup(aWB As Workbook, ByRef WKSChart As Worksheet, _
gt; ByRef ChartSeriesData As Worksheet)
gt; 'Should actually check if these sheets exist
gt; Set WKSChart = aWB.Worksheets.Add
gt; Set ChartSeriesData = aWB.Worksheets.Add
gt; WKSChart.Name = quot;WKS Chartsquot;
gt; ChartSeriesData.Name = quot;Chart Seriesquot;
gt; 'also need to add headers
gt; End Sub
gt; Sub writeChartInfo(ByRef TargCell As Range, _
gt; aChart As Chart)
gt; 'write data of interest in row of targcell, then
gt; Set TargCell = TargCell.Offset(1, 0)
gt; End Sub
gt; Sub writeSeriesInfo(ByRef TargCell As Range, _
gt; aChart As Chart)
gt; Dim aSeries As Series
gt; For Each aSeries In aChart.SeriesCollection
gt; 'write chart series info to TargCell row, then
gt; Set TargCell = TargCell.Offset(1, 0)
gt; Next aSeries
gt; End Sub
gt; Sub analyzeAllEmbeddedCharts()
gt; Dim aWS As Worksheet, aChartObj As ChartObject, _
gt; WKSChart As Worksheet, ChartSeriesData As Worksheet, _
gt; ChartWKSCell As Range, SeriesWKSCell As Range
gt; InitSetup ActiveWorkbook, WKSChart, ChartSeriesData
gt; Set ChartWKSCell = WKSChart.Cells(2, 1)
gt; Set SeriesWKSCell = ChartSeriesData.Cells(2, 1)
gt; For Each aWS In ActiveWorkbook.Worksheets
gt; For Each aChartObj In aWS.ChartObjects
gt; writeChartInfo ChartWKSCell, aChartObj.Chart
gt; writeSeriesInfo SeriesWKSCell, aChartObj.Chart
gt; Next aChartObj
gt; Next aWS
gt; End Sub
gt;
gt; --
gt; Regards,
gt;
gt; Tushar Mehta
gt; www.tushar-mehta.com
gt; Excel, PowerPoint, and VBA add-ins, tutorials
gt; Custom MS Office productivity solutions
gt;
gt; In article gt;,
gt; says...
gt; gt; I'm wondering if the charting group can assist.
gt; gt;
gt; gt; quot;Barb Reinhardtquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; If I have a list of worksheets in a workbook, with VBA, how do I determine
gt; gt; gt; the following:
gt; gt; gt;
gt; gt; gt; 1) If there is a chart on the worksheet and the chart identification
gt; gt; gt; 2) If there is a chart ... how do I determine the number of series
gt; gt; gt; displayed in the chart And how do I display the SERIES #, the XVAL and the
gt; gt; gt; YVAL for each series?
gt; gt; gt;
gt; gt; gt; Thanks in advance,
gt; gt; gt; Barb Reinhardt
gt; gt;
gt; gt;
gt; gt;
gt;

Hi Barb,

I don't know how much VBA you know. Consequently, I don't know how
much detailed help you need.

To get the X-values themselves use the XValues property of the series.
The Y-values are available through the Values property. However, this
will yield the actual numeric values themselves. If you want to know
what range/name the series contains you will have to use something like
John Walkenbach's www.j-walk.com/ss/excel/tips/tip83.htm There
may be a way to get the information more directly witha XLM macro but I
don't know how.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article gt;,says...
gt; Tushar,
gt;
gt; That gets me a lot closer. What I want to know is programmatically, how do
gt; I identify the X and Y axis entries for the chart and series of interest and
gt; print them out. What I want is
gt;
gt; WORKSHEET NAME CHART NAME SERIES # X VALUES Y VALUES
gt;
gt; on a spreadsheet. How do I find out how to do this?
gt;
gt; Thanks,
gt; Barb Reinhardt
gt;
gt; quot;Tushar Mehtaquot; wrote:
gt;
gt; gt; All you have to do is add the code to write out the information of
gt; gt; interest to you.
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub InitSetup(aWB As Workbook, ByRef WKSChart As Worksheet, _
gt; gt; ByRef ChartSeriesData As Worksheet)
gt; gt; 'Should actually check if these sheets exist
gt; gt; Set WKSChart = aWB.Worksheets.Add
gt; gt; Set ChartSeriesData = aWB.Worksheets.Add
gt; gt; WKSChart.Name = quot;WKS Chartsquot;
gt; gt; ChartSeriesData.Name = quot;Chart Seriesquot;
gt; gt; 'also need to add headers
gt; gt; End Sub
gt; gt; Sub writeChartInfo(ByRef TargCell As Range, _
gt; gt; aChart As Chart)
gt; gt; 'write data of interest in row of targcell, then
gt; gt; Set TargCell = TargCell.Offset(1, 0)
gt; gt; End Sub
gt; gt; Sub writeSeriesInfo(ByRef TargCell As Range, _
gt; gt; aChart As Chart)
gt; gt; Dim aSeries As Series
gt; gt; For Each aSeries In aChart.SeriesCollection
gt; gt; 'write chart series info to TargCell row, then
gt; gt; Set TargCell = TargCell.Offset(1, 0)
gt; gt; Next aSeries
gt; gt; End Sub
gt; gt; Sub analyzeAllEmbeddedCharts()
gt; gt; Dim aWS As Worksheet, aChartObj As ChartObject, _
gt; gt; WKSChart As Worksheet, ChartSeriesData As Worksheet, _
gt; gt; ChartWKSCell As Range, SeriesWKSCell As Range
gt; gt; InitSetup ActiveWorkbook, WKSChart, ChartSeriesData
gt; gt; Set ChartWKSCell = WKSChart.Cells(2, 1)
gt; gt; Set SeriesWKSCell = ChartSeriesData.Cells(2, 1)
gt; gt; For Each aWS In ActiveWorkbook.Worksheets
gt; gt; For Each aChartObj In aWS.ChartObjects
gt; gt; writeChartInfo ChartWKSCell, aChartObj.Chart
gt; gt; writeSeriesInfo SeriesWKSCell, aChartObj.Chart
gt; gt; Next aChartObj
gt; gt; Next aWS
gt; gt; End Sub
gt; gt;
gt; gt; --
gt; gt; Regards,
gt; gt;
gt; gt; Tushar Mehta
gt; gt; www.tushar-mehta.com
gt; gt; Excel, PowerPoint, and VBA add-ins, tutorials
gt; gt; Custom MS Office productivity solutions
gt; gt;
gt; gt; In article gt;,
gt; gt; says...
gt; gt; gt; I'm wondering if the charting group can assist.
gt; gt; gt;
gt; gt; gt; quot;Barb Reinhardtquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; If I have a list of worksheets in a workbook, with VBA, how do I determine
gt; gt; gt; gt; the following:
gt; gt; gt; gt;
gt; gt; gt; gt; 1) If there is a chart on the worksheet and the chart identification
gt; gt; gt; gt; 2) If there is a chart ... how do I determine the number of series
gt; gt; gt; gt; displayed in the chart And how do I display the SERIES #, the XVAL and the
gt; gt; gt; gt; YVAL for each series?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advance,
gt; gt; gt; gt; Barb Reinhardt
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt;

I don't think XLM is any better than John's class module. John's approach is
a bit tricky to understand at first, but it's very useful.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
PeltierTech.com/
_______quot;Tushar Mehtaquot; gt; wrote in message om...
gt; Hi Barb,
gt;
gt; I don't know how much VBA you know. Consequently, I don't know how
gt; much detailed help you need.
gt;
gt; To get the X-values themselves use the XValues property of the series.
gt; The Y-values are available through the Values property. However, this
gt; will yield the actual numeric values themselves. If you want to know
gt; what range/name the series contains you will have to use something like
gt; John Walkenbach's www.j-walk.com/ss/excel/tips/tip83.htm There
gt; may be a way to get the information more directly witha XLM macro but I
gt; don't know how.
gt;
gt; --
gt; Regards,
gt;
gt; Tushar Mehta
gt; www.tushar-mehta.com
gt; Excel, PowerPoint, and VBA add-ins, tutorials
gt; Custom MS Office productivity solutions
gt;
gt; In article gt;,
gt; says...
gt;gt; Tushar,
gt;gt;
gt;gt; That gets me a lot closer. What I want to know is programmatically, how
gt;gt; do
gt;gt; I identify the X and Y axis entries for the chart and series of interest
gt;gt; and
gt;gt; print them out. What I want is
gt;gt;
gt;gt; WORKSHEET NAME CHART NAME SERIES # X VALUES Y VALUES
gt;gt;
gt;gt; on a spreadsheet. How do I find out how to do this?
gt;gt;
gt;gt; Thanks,
gt;gt; Barb Reinhardt
gt;gt;
gt;gt; quot;Tushar Mehtaquot; wrote:
gt;gt;
gt;gt; gt; All you have to do is add the code to write out the information of
gt;gt; gt; interest to you.
gt;gt; gt;
gt;gt; gt; Option Explicit
gt;gt; gt; Sub InitSetup(aWB As Workbook, ByRef WKSChart As Worksheet, _
gt;gt; gt; ByRef ChartSeriesData As Worksheet)
gt;gt; gt; 'Should actually check if these sheets exist
gt;gt; gt; Set WKSChart = aWB.Worksheets.Add
gt;gt; gt; Set ChartSeriesData = aWB.Worksheets.Add
gt;gt; gt; WKSChart.Name = quot;WKS Chartsquot;
gt;gt; gt; ChartSeriesData.Name = quot;Chart Seriesquot;
gt;gt; gt; 'also need to add headers
gt;gt; gt; End Sub
gt;gt; gt; Sub writeChartInfo(ByRef TargCell As Range, _
gt;gt; gt; aChart As Chart)
gt;gt; gt; 'write data of interest in row of targcell, then
gt;gt; gt; Set TargCell = TargCell.Offset(1, 0)
gt;gt; gt; End Sub
gt;gt; gt; Sub writeSeriesInfo(ByRef TargCell As Range, _
gt;gt; gt; aChart As Chart)
gt;gt; gt; Dim aSeries As Series
gt;gt; gt; For Each aSeries In aChart.SeriesCollection
gt;gt; gt; 'write chart series info to TargCell row, then
gt;gt; gt; Set TargCell = TargCell.Offset(1, 0)
gt;gt; gt; Next aSeries
gt;gt; gt; End Sub
gt;gt; gt; Sub analyzeAllEmbeddedCharts()
gt;gt; gt; Dim aWS As Worksheet, aChartObj As ChartObject, _
gt;gt; gt; WKSChart As Worksheet, ChartSeriesData As Worksheet, _
gt;gt; gt; ChartWKSCell As Range, SeriesWKSCell As Range
gt;gt; gt; InitSetup ActiveWorkbook, WKSChart, ChartSeriesData
gt;gt; gt; Set ChartWKSCell = WKSChart.Cells(2, 1)
gt;gt; gt; Set SeriesWKSCell = ChartSeriesData.Cells(2, 1)
gt;gt; gt; For Each aWS In ActiveWorkbook.Worksheets
gt;gt; gt; For Each aChartObj In aWS.ChartObjects
gt;gt; gt; writeChartInfo ChartWKSCell, aChartObj.Chart
gt;gt; gt; writeSeriesInfo SeriesWKSCell, aChartObj.Chart
gt;gt; gt; Next aChartObj
gt;gt; gt; Next aWS
gt;gt; gt; End Sub
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; Regards,
gt;gt; gt;
gt;gt; gt; Tushar Mehta
gt;gt; gt; www.tushar-mehta.com
gt;gt; gt; Excel, PowerPoint, and VBA add-ins, tutorials
gt;gt; gt; Custom MS Office productivity solutions
gt;gt; gt;
gt;gt; gt; In article gt;,
gt;gt; gt; says...
gt;gt; gt; gt; I'm wondering if the charting group can assist.
gt;gt; gt; gt;
gt;gt; gt; gt; quot;Barb Reinhardtquot; gt; wrote in
gt;gt; gt; gt; message
gt;gt; gt; gt; ...
gt;gt; gt; gt; gt; If I have a list of worksheets in a workbook, with VBA, how do I
gt;gt; gt; gt; gt; determine
gt;gt; gt; gt; gt; the following:
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; 1) If there is a chart on the worksheet and the chart
gt;gt; gt; gt; gt; identification
gt;gt; gt; gt; gt; 2) If there is a chart ... how do I determine the number of series
gt;gt; gt; gt; gt; displayed in the chart And how do I display the SERIES #, the XVAL
gt;gt; gt; gt; gt; and the
gt;gt; gt; gt; gt; YVAL for each series?
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Thanks in advance,
gt;gt; gt; gt; gt; Barb Reinhardt
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt;
gt;gt;