Remove (or Hide) Points in a Graph with VBA

[alex.osbaldeston]

Hi All,

Be grateful for a bit of VBA assistance here. I'm trying to automate
the updating of a PowerPoint presentation from an Excel macro. Most of
it, I'm happy with and I can happily update the datasheets behind MS
Graph charts and all is going well. What I also wanted to do though was
remove the oldest column of data from the datasheet as my macro is
going to be adding a new column of data on the right-hand sided of the
datasheet (so it still covers the same timespan). Basically I need to
find out how you remove Points from a graph.

To explain a bit further, say I have a MS Graph chart with 5 rows and 5
columns of data. The rows are the SeriesCollections and the columns are
the Points (as I understand it).

The macro to update the chart values might be something like this:

Sub code_extract()

Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim oGraph As Graph.Chart
Dim oPPTFile As PowerPoint.Presentation

Set oPPTApp = CreateObject("PowerPoint.Application")
oPPTApp.Visible = msoTrue

Set oPPTFile =
oPPTApp.Presentations.Open(Filename:=c:\template.ppt)

Set oPPTShape = oPPTFile.Slides(9).Shapes("Object 2")
Set oGraph = oPPTShape.OLEFormat.Object

oGraph.Application.DataSheet.Range("M1").Value = Cells(2, 2).Value
oGraph.Application.DataSheet.Range("M2").Value = Cells(3, 2).Value
oGraph.Application.DataSheet.Range("M3").Value = Cells(4, 2).Value
oGraph.Application.DataSheet.Range("M4").Value = Cells(5, 2).Value
oGraph.Application.DataSheet.Range("M5").Value = Cells(6, 2).Value

oGraph.Application.Update
oGraph.Application.Quit

End Sub

Now, somewhere in there I want to delete all the data in the datasheet
in a particular column. If it was the other way around and I wanted to
remove say row 3 of data, I would just add:

oGraph.SeriesCollection(3).Delete

This doesn't actually delete any real data either, just Greys it out in
the datasheet so it's not displayed in the chart which is ideal
frankly. I just need to know how to do the same thing for the columns.
This doesn't work:

oGraph.Points(1).Delete

I'd be grateful for any other ideas.

Many thanks
Alex