Possible to Add Horizontal Line to Embedded Graph via VBA?

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

Maybe you could post the code you have so far.
Don't know about Access but id your chart has the same object model as an
Excel chart then NewSeries should work.

Try recording a macro in excel doing the same thing and see what you get.
That might be a good start.

Tim.



--
Tim Williams
Palo Alto, CA


"TimSchwaar" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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).OLEFormat

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 procedure:

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).OLEFormat

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
>>
>

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).OLEFormat
>
> 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 procedure:
>
> 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).OLEFormat
>
> 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
> >>
> >

Tim -

Glad to help. I've only recently figured out this OLE stuff myself.

One thing to remember is that Excel's charting engine is much like MS Graph's, in
fact, Graph's is a somewhat watered down version of Excel's.

In Excel, you can plot data from practically anywhere in a chart. You use the
SeriesCollection.Add or .NewSeries methods, and then take the data from any range on
any sheet or even from a named range or an array of values.

In MSG, I don't thing .Add or .NewSeries are meaningful. Whatever's in the datasheet
becomes part of the chart. So it was a matter of putting the necessary data into the
datasheet.

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

TimSchwaar wrote:
> 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).OLEFormat
>>
>>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 procedure:
>>
>>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).OLEFormat
>>
>> 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
>>>>
>>>
>