Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating PowerPoint Graph in Access

Status
Not open for further replies.

TomHW

Programmer
Sep 8, 2004
100
US
Using automation I am able to create a graph object in PowerPoint from Access, either from scratch or based upon a graph template, and to fill its datasheet from code.

The problem is that I have to update the datasheet one cell at a time. I have 1700 data points that need to be graphed, which ends up taking between 15 and 30 minutes to do one at a time.

Is there any way create a graph in Access and then, through code, set either the powerpoint graph equal to the Access graph or even the datasheet of the one equal to the datasheet of the other?

A bit of code to help:
Code:
[COLOR=green]
' Create a new Graph object on the slide.[/color]
Set objPPPres = objPPApp.Presentations.Add.Slides.Add(1, 12)

lngHeight = (objPPApp.ActivePresentation.PageSetup.SlideHeight / 3) * 2
lngWidth = (objPPApp.ActivePresentation.PageSetup.SlideWidth / 3) * 2
lngLeft = (objPPApp.ActivePresentation.PageSetup.SlideHeight / 6)
lngTop = (objPPApp.ActivePresentation.PageSetup.SlideHeight / 6)

Set shpGraph = objPPPres.Shapes.AddOLEObject(Left:=lngLeft, _
         Top:=lngTop, Width:=lngWidth, Height:=lngHeight, _
         ClassName:="MSGraph.Chart", Link:=0).OLEFormat.Object


[COLOR=green]' Set the reference to the datasheet collection.[/color]
Set objData = shpGraph.Application.DataSheet

[COLOR=green]' Clear the datasheet.[/color]
objData.Cells.Clear

[COLOR=green]' Loop through the recordset rs based upon a table[/color]
Do While Not rs.EOF
    clngField = 1

    [COLOR=green]' Put the values for the fields in the datasheet.[/color]
    For Each fld In rs.Fields
        objData.Cells(clngField, clngRow + 1).Value = _
            rs.Fields(clngField - 1).Value
        clngField = clngField + 1
    Next fld
    clngRow = clngRow + 1
    rs.MoveNext
Loop

[COLOR=green]' Update the graph.[/color]
shpGraph.Application.Update
DoEvents

Since I am able to manipulate each cell of the graph in powerpoint, is there a way to set the datasheet equal to the datasheet of a graph in access to speed up this event?

Any help is greatly appreciated,
Tom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top