Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
TimSchwaar
 
Posts: n/a
Default 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

  #2   Report Post  
Tim Williams
 
Posts: n/a
Default

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" wrote in message
oups.com...
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



  #3   Report Post  
Andy Pope
 
Posts: n/a
Default

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
  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

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


  #5   Report Post  
TimSchwaar
 
Posts: n/a
Default

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





  #6   Report Post  
Jon Peltier
 
Posts: n/a
Default

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




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
strange horizontal line showing accross several columns Thrava Excel Discussion (Misc queries) 4 June 29th 05 03:33 AM
line graph ratio Charts and Charting in Excel 1 June 23rd 05 12:41 AM
Colour part of a line on an X Y graph ExcelMonkey Charts and Charting in Excel 1 May 26th 05 10:57 AM
Line Graph Data Recognition Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM
Line and Column Graph Nikki Charts and Charting in Excel 3 February 4th 05 05:42 AM


All times are GMT +1. The time now is 09:31 PM.

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"