How to find a Chart embedded in a Sheet?

[Gilroy]

Consider the following chart (this is the macro used to
create the chart):

'---------------------------------------------
------------------------
Charts.Add()
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData(Source:=Sheets
("Sheet1").Range("A7:C13"), PlotBy _
:=xlColumns)
ActiveChart.Location
(Where:=xlLocationAsObject, Name:="Sheet1")
ActiveSheet.Shapes("Chart 2").IncrementLeft(-
138.75)
ActiveSheet.Shapes("Chart 2").IncrementTop
(26.25)
ActiveWindow.Visible = False
Windows("projected.xls").Activate()
Range("G14").Select()
'=============================================
========================

I've successfully confirmed the .Top and .Left positions
of the Shape, but I can't seem to drill down to the
actual Chart object that was embedded.

I need to verify
the .ChartType, .SeriesCollection.Formula
and .SeriesCollection.Name of this embedded chart, but I
can't seem to find my way to it.

Can someone help me locate the Chart within the Shape?

Thanks, in advance, for your help.

 

[Mark Thorpe]

Hi Gilroy -

Not sure if this completely answers your question, but to get to a chart
embedded in a worksheet (as you have here), you'll want to go through the
ChartObjects collection of the Worksheet object. For example, the following
code will get you the last Chart on the active worksheet (if there are more
than one)

Dim mychart As Chart
Dim icount As Integer

icount = ActiveSheet.ChartObjects.Count
If icount > 0 Then
Set mychart = ActiveSheet.ChartObjects(icount).Chart
MsgBox CStr(mychart.ChartType)
End If