Re: Chart VBA Question

[Barb Reinhardt]

I'm wondering if the charting group can assist.

"Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
news:2DBE1B1F-95E9-4498-909D-4FB0B9F2E7F0@microsoft.com...
> 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

[Tushar Mehta]

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 <ulVu3qwFGHA.2064@TK2MSFTNGP09.phx.gbl>,
reply@tonewsgroup.com says...
> I'm wondering if the charting group can assist.
>
> "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
> news:2DBE1B1F-95E9-4498-909D-4FB0B9F2E7F0@microsoft.com...
> > 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
>
>
>

[Barb Reinhardt]

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 <ulVu3qwFGHA.2064@TK2MSFTNGP09.phx.gbl>,
> reply@tonewsgroup.com says...
> > I'm wondering if the charting group can assist.
> >
> > "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
> > news:2DBE1B1F-95E9-4498-909D-4FB0B9F2E7F0@microsoft.com...
> > > 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
> >
> >
> >
>

[Tushar Mehta]

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 <6DFF086B-857C-49A2-9A93-FD8FE9CFC50C@microsoft.com>,
BarbReinhardt@discussions.microsoft.com 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 <ulVu3qwFGHA.2064@TK2MSFTNGP09.phx.gbl>,
> > reply@tonewsgroup.com says...
> > > I'm wondering if the charting group can assist.
> > >
> > > "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
> > > news:2DBE1B1F-95E9-4498-909D-4FB0B9F2E7F0@microsoft.com...
> > > > 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
> > >
> > >
> > >
> >
>

[Jon Peltier]

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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1e304a4df5bb6cee98b2de@msnews.microsoft.com...
> 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 <6DFF086B-857C-49A2-9A93-FD8FE9CFC50C@microsoft.com>,
> BarbReinhardt@discussions.microsoft.com 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 <ulVu3qwFGHA.2064@TK2MSFTNGP09.phx.gbl>,
>> > reply@tonewsgroup.com says...
>> > > I'm wondering if the charting group can assist.
>> > >
>> > > "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in
>> > > message
>> > > news:2DBE1B1F-95E9-4498-909D-4FB0B9F2E7F0@microsoft.com...
>> > > > 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
>> > >
>> > >
>> > >
>> >
>>