BINGO! Thanks SO MUCH, Jon. With just a few changes to refer to my
Access objects specifically, your code worked perfectly. Andy, you
were right also: I needed to be adding to the Datasheet object. I
thought I would be able to do that directly by adding a new series, not
recognizing that the Series just describe what the chart creates FROM
the datasheet. This works just fine for our proof of concept. Thanks
again, Jon.

Tim Schwaar

Jon Peltier wrote:
You can reference the MSG object in PowerPoint (you'll have to figure
out the Access equivalents) using

Set obOLE = ppApp.ActivePresentation.Slides(2).Shapes(1).OLEFo rmat

The chart is referenced as

obOLE.Object

You can find out how many series there are like this:

nSeries = obOLE.SeriesCollection.Count

and the number of points as

nPoints = obOLE.SeriesCollection(1).Points.Count

This helps you figure out which cells of the data sheet to populate with
the average values.

I wrote this piece of code to do what you want, with an MSG chart in
PowerPoint, which can serve as the basis of your procedu

Sub PPT_MSG_OLE()
Dim ppApp As PowerPoint.Application
Dim obOLE As PowerPoint.OLEFormat
Dim nSeries As Integer
Dim nPoints As Integer
Dim iPoints As Integer
Dim lPlotBy As Long

Set ppApp = GetObject(, "PowerPoint.Application")
Set obOLE = ppApp.ActivePresentation.Slides(2).Shapes(1).OLEFo rmat

With obOLE.Object
nSeries = .SeriesCollection.Count
nPoints = .SeriesCollection(1).Points.Count
End With

With obOLE.Object.Application
Select Case .PlotBy
Case 1 ' by rows
With .DataSheet
.Cells(nSeries + 2, 1).Value = "Added Series"
For iPoints = 1 To nPoints
.Cells(nSeries + 2, iPoints + 1).Value = 10 ' average
Next
End With
Case 2 ' by columns
With .DataSheet
.Cells(1, nSeries + 2).Value = "Added Series"
For iPoints = 1 To nPoints
.Cells(iPoints + 1, nSeries + 2).Value = 15 ' average
Next
End With
End Select
End With

On Error Resume Next
' skip if it fails, as in a 3D chart
obOLE.Object.SeriesCollection(nSeries + 1).ChartType = xlLine

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Andy Pope wrote:

Hi,

If it is the MSGraph object maybe you should look at adding data to the
Datasheet object.

Cheers
Andy

TimSchwaar wrote:

Hello -

We have an Access 2000 database (don't leave yet!) with a report that
is basically just an embedded (OLE) graph object. It is a simple line
graph, based on an Access pivot query, showing three lines (series)
with numerous data points.

We want to add a horizontal 'target' line to this graph so people can
see the data relative to a target. Manipulating the underlying data is
difficult only because there are quite a few similar reports, each with
complex queries. We had hoped to manipulate the graph/chart directly
from VBA by adding an additional series with our own target value as
the data.

Our problem is our inability to add a series. The Chart object,
Series, SeriesCollection, etc. don't have an Add method. An approach
I've seen in a few examples (after LOTS of Google searching) involved
the .NewSeries method but that produces an error message indicating an
unknown method.

Is our approach completely wrong? Is it even possible to add another
series to an existing chart? Thanks for any help.

Tim Schwaar