Export Graph to Powerpoint via Code

[Guest]

Wondering if anyone can help me with some code.

I'd like to either
A)Place a Microsoft Graph object ("Graph1") from an existing report
"Report1" to a Powerpoint presentation "C:\Test.ppt" and update the datasheet
of the Graph object with a query "Query1" OR

B)I can copy the graph object into a PPT manually, but would need to open
the PPT "C:\test.ppt", select the object, and update the data with a query.
This would need to be done from within ACCESS.

Can anybody provide some sample code that could move me in the right
direction?

 

[Al Borges]

Hi Troy:

Check out the MSDN article ("ACC2000: How to Create an MS Graph in
PowerPoint Using Access") at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;200551

It includes a discussion on the process of updating the graph.

Regards,
Al

 

[Guest]

Al,
Thanks for the link, that filled in the missing pieces.

I know this isn't the best code but it works and is relatively simple. In
case anyone is interested in the code I used to solve this problem here it
is. This function accepts a Query Name (that you want the source of the
graph to be) and the Slide number that the PPt Graph is on. It will pop up
the open file dialog and allow you to select the presentation.

Sub UpdatePowerpointGraph(QueryName As String, SlideNumber As Integer)

Dim MyChart As Object
Dim MyFiles As FileDialog 'the standard OpenFile Dialog box
Dim Pres As Object

' Create the Powerpoint application.
Set Pres = CreateObject("Powerpoint.Application")

'Show the open file dialog box
Set MyFiles = Application.FileDialog(msoFileDialogFilePicker)
MyFiles.AllowMultiSelect = False
MyFiles.Show

'Activate and open
Pres.Activate 'shows the Powerpoint, code won't work w/o this
Pres.Presentations.Open MyFiles.SelectedItems(1)
Pres.activepresentation.slides(1).Select

'Find the graph on the slide
For Shpcnt = 1 To Pres.activepresentation.slides(SlideNumber).Shapes.Count
' Check if shape is an OLE object.
If Pres.activepresentation.slides(SlideNumber).Shapes(Shpcnt).Type =
7 Then
Set MyChart =
Pres.activepresentation.slides(SlideNumber).Shapes(Shpcnt).OLEFormat.Object
End If
Next Shpcnt

'Copies the query, could do it with nested loops and one cell at a time
if desired
DoCmd.OpenQuery (QueryName)
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.Close

'Clear the chart data and paste the new data
MyChart.Application.DataSheet.Cells.Clear
MyChart.Application.DataSheet.Range("00").Paste
MyChart.Application.Update

'save and close powerpoint
Pres.activepresentation.Save
Pres.Quit

'clean up objects
Set Pres = Nothing
Set MyReport = Nothing
Set MyChart = Nothing
End Sub

 

[Al Borges]

Great! Thanks, Troy.

Al

Al,
Thanks for the link, that filled in the missing pieces.

I know this isn't the best code but it works and is relatively simple. In
case anyone is interested in the code I used to solve this problem here it
is. This function accepts a Query Name (that you want the source of the
graph to be) and the Slide number that the PPt Graph is on. It will pop
up
the open file dialog and allow you to select the presentation.

Sub UpdatePowerpointGraph(QueryName As String, SlideNumber As Integer)

Dim MyChart As Object
Dim MyFiles As FileDialog 'the standard OpenFile Dialog box
Dim Pres As Object

' Create the Powerpoint application.
Set Pres = CreateObject("Powerpoint.Application")

'Show the open file dialog box
Set MyFiles = Application.FileDialog(msoFileDialogFilePicker)
MyFiles.AllowMultiSelect = False
MyFiles.Show

'Activate and open
Pres.Activate 'shows the Powerpoint, code won't work w/o this
Pres.Presentations.Open MyFiles.SelectedItems(1)
Pres.activepresentation.slides(1).Select

'Find the graph on the slide
For Shpcnt = 1 To
Pres.activepresentation.slides(SlideNumber).Shapes.Count
' Check if shape is an OLE object.
If Pres.activepresentation.slides(SlideNumber).Shapes(Shpcnt).Type
=
7 Then
Set MyChart =
Pres.activepresentation.slides(SlideNumber).Shapes(Shpcnt).OLEFormat.Object
End If
Next Shpcnt

'Copies the query, could do it with nested loops and one cell at a time
if desired
DoCmd.OpenQuery (QueryName)
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.Close

'Clear the chart data and paste the new data
MyChart.Application.DataSheet.Cells.Clear
MyChart.Application.DataSheet.Range("00").Paste
MyChart.Application.Update

'save and close powerpoint
Pres.activepresentation.Save
Pres.Quit

'clean up objects
Set Pres = Nothing
Set MyReport = Nothing
Set MyChart = Nothing
End Sub