Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
strange horizontal line showing accross several columns | Excel Discussion (Misc queries) | |||
line graph | Charts and Charting in Excel | |||
Colour part of a line on an X Y graph | Charts and Charting in Excel | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Line and Column Graph | Charts and Charting in Excel |