Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,355
Default Chart VBA Question

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
  #2   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default Chart VBA Question

Hi Barb,

Sub test()
Dim nCnt As Long
Dim chtObj As ChartObject
Dim sr As Series
Dim vY, vX
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
nCnt = 0
For Each chtObj In ActiveSheet.ChartObjects
nCnt = nCnt + 1
With chtObj.Chart
MsgBox "Chart-Name : " & .Name & vbCr & _
"No of Series : " & .SeriesCollection.Count, , ws.Name

For Each sr In .SeriesCollection
If IsArray(vY) Then Erase vY
If IsArray(vX) Then Erase vX
vY = sr.Values
vX = sr.XValues
''what do you want to do with these ?
Next
End With
Next
If nCnt = 0 Then
MsgBox "No Charts in " & ws.Name
End If
Next
End Sub

I don't know what you mean by -

And how do I display the SERIES #, the XVAL and the
YVAL for each series?


but maybe you can adapt the above to do what you want.

Regards,
Peter T

"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



  #4   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
external usenet poster
 
Posts: 1,071
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
external usenet poster
 
Posts: 3,355
Default 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







  #6   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
external usenet poster
 
Posts: 1,071
Default 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





  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default Chart VBA Question

I added the Worksheets loop at the last moment and forgot to change -

For Each chtObj In ActiveSheet.ChartObjects


to

For Each chtObj In ws.ChartObjects


You added a little more about your requirements in your reply to Tushar, but
what do you mean by "SERIES #", the actual names of each series perhaps. If
so include something like this when looping the SeriesCollection

serName = sr.Name

With the combination of routines suggested to you I think there's enough for
you to piece something together to suit your requirements. Though you would
need to carefully keep track of where you are writing details to cells on
the worksheet, not only with each chart but additionally when writing
multiple sets of series values, assuming that's what you want to do.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Barb,

Sub test()
Dim nCnt As Long
Dim chtObj As ChartObject
Dim sr As Series
Dim vY, vX
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
nCnt = 0
For Each chtObj In ActiveSheet.ChartObjects
nCnt = nCnt + 1
With chtObj.Chart
MsgBox "Chart-Name : " & .Name & vbCr & _
"No of Series : " & .SeriesCollection.Count, , ws.Name

For Each sr In .SeriesCollection
If IsArray(vY) Then Erase vY
If IsArray(vX) Then Erase vX
vY = sr.Values
vX = sr.XValues
''what do you want to do with these ?
Next
End With
Next
If nCnt = 0 Then
MsgBox "No Charts in " & ws.Name
End If
Next
End Sub

I don't know what you mean by -

And how do I display the SERIES #, the XVAL and the
YVAL for each series?


but maybe you can adapt the above to do what you want.

Regards,
Peter T

"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





  #8   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default 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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Chart Question carl Charts and Charting in Excel 5 November 6th 11 12:24 PM
Chart Question Xl2007 Charts and Charting in Excel 1 November 11th 08 05:31 AM
chart question Doug W Excel Discussion (Misc queries) 1 January 25th 07 06:16 AM
Chart question Johnfli Excel Discussion (Misc queries) 3 May 20th 06 06:02 PM
chart question Cowtoon Charts and Charting in Excel 1 February 14th 06 01:56 AM


All times are GMT +1. The time now is 10:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"