Chart VBA Question
 

I'm wondering if the charting group can assist.

"Barb Reinhardt" wrote in message
...
If I have a list of worksheets in a workbook, with VBA, how do I determine
the following:

1) If there is a chart on the worksheet and the chart identification
2) If there is a chart ... how do I determine the number of series
displayed in the chart And how do I display the SERIES #, the XVAL and the
YVAL for each series?

Thanks in advance,
Barb Reinhardt

Chart VBA Question
 

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 = "WKS Charts"
ChartSeriesData.Name = "Chart Series"
'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 ,
says...
I'm wondering if the charting group can assist.

"Barb Reinhardt" wrote in message
...
If I have a list of worksheets in a workbook, with VBA, how do I determine
the following:

1) If there is a chart on the worksheet and the chart identification
2) If there is a chart ... how do I determine the number of series
displayed in the chart And how do I display the SERIES #, the XVAL and the
YVAL for each series?

Thanks in advance,
Barb Reinhardt

Chart VBA Question
 

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

"Tushar Mehta" wrote:

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 = "WKS Charts"
ChartSeriesData.Name = "Chart Series"
'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 ,
says...
I'm wondering if the charting group can assist.

"Barb Reinhardt" wrote in message
...
If I have a list of worksheets in a workbook, with VBA, how do I determine
the following:

1) If there is a chart on the worksheet and the chart identification
2) If there is a chart ... how do I determine the number of series
displayed in the chart And how do I display the SERIES #, the XVAL and the
YVAL for each series?

Thanks in advance,
Barb Reinhardt

Chart VBA Question
 

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 http://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 ,
says...
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

"Tushar Mehta" wrote:

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 = "WKS Charts"
ChartSeriesData.Name = "Chart Series"
'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 ,
says...
I'm wondering if the charting group can assist.

"Barb Reinhardt" wrote in message
...
If I have a list of worksheets in a workbook, with VBA, how do I determine
the following:

1) If there is a chart on the worksheet and the chart identification
2) If there is a chart ... how do I determine the number of series
displayed in the chart And how do I display the SERIES #, the XVAL and the
YVAL for each series?

Thanks in advance,
Barb Reinhardt

Chart VBA Question
 

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
http://PeltierTech.com/
_______


"Tushar Mehta" wrote in message
om...
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 http://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 ,
says...
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

"Tushar Mehta" wrote:

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 = "WKS Charts"
ChartSeriesData.Name = "Chart Series"
'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 ,
says...
I'm wondering if the charting group can assist.

"Barb Reinhardt" wrote in
message
...
If I have a list of worksheets in a workbook, with VBA, how do I
determine
the following:

1) If there is a chart on the worksheet and the chart
identification
2) If there is a chart ... how do I determine the number of series
displayed in the chart And how do I display the SERIES #, the XVAL
and the
YVAL for each series?

Thanks in advance,
Barb Reinhardt