Working with PowerPoint Presentations from Access Using Automation

Frank Rice
Tim Getsch
Microsoft Corporation

January 2005

Applies to:
    Microsoft Office Access 2000 or later
    Microsoft Office PowerPoint 2003

Summary: Create a PowerPoint slide presentation from scratch using Access data. In addition, display and control a slide show from within an Access form. Walk through the solution and explore ways to extend the sample for your own applications. (8 printed pages)

Download OfficeAccess2PowerPointSample.exe.

Introduction
Creating a PowerPoint Presentation from Access Data
Using Automation to Display a PowerPoint Presentation in a Form
Conclusion
Additional Resources

An Office customer recently asked the following question: I manually create Microsoft Office PowerPoint slides on a weekly basis from data in Microsoft Office Access. Is there a way I can automate this task? Well, the immediate answer is that you can't export data directly from Access to PowerPoint. However, you can streamline the task, depending on your needs and desire to write Microsoft Visual Basic for Applications (VBA) code.

This article looks at two ways of interacting between Access and PowerPoint. The first sample illustrates how to create a PowerPoint presentation from the data in an Access table using Automation. The second sample shows how to display and manipulate an existing PowerPoint presentation inside of an Access form, also using Automation. Automation gives you the ability to control one application from another by manipulating the controlled application's exposed properties and methods, and responding to events.

This sample creates a slide presentation using Access data. A Recordset object is created from the data in a table. The data from that recordset is then used to populate a slide show.

To create the slide show, do the following:

1. Start Access and open any database.

2. In Design view, create the following form not based on any table or query, with the control indicated:

   Form: CreateFromAccessData

   Caption: PowerPoint Demo

   Command Button: cmdPowerPoint

   • Name: cmdPowerPoint
   • Caption: PowerPoint Example
   • Width: 2"

3. On the View menu, click Code.

4. On the Tools menu, click References.

5. In the Available References box, click Microsoft PowerPoint 9.0 Object Library and Microsoft Office 9.0 Object Library.

6. Click OK to close the References dialog box.

7. Add the following line of code to the General Declarations section:

   Option Explicit
   

8. Type or paste the following procedure:

   Sub cmdPowerPoint_Click()
       Dim db As Database, rs As Recordset
       Dim ppObj As PowerPoint.Application
       Dim ppPres As PowerPoint.Presentation
   
       On Error GoTo err_cmdOLEPowerPoint
   
       ' Open up a recordset on the Employees table.
       Set db = CurrentDb
       Set rs = db.OpenRecordset("Employees", dbOpenDynaset)
   
       ' Open up an instance of Powerpoint.
       Set ppObj = New PowerPoint.Application
       Set ppPres = ppObj.Presentations.Add
   
       ' Setup the set of slides and populate them with data from the
       ' set of records.
       With ppPres
           While Not rs.EOF
               With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
                   .Shapes(1).TextFrame.TextRange.Text = "Hi!  Page " & rs.AbsolutePosition + 1
                   .SlideShowTransition.EntryEffect = ppEffectFade
                   With .Shapes(2).TextFrame.TextRange
                       .Text = CStr(rs.Fields("LastName").Value)
                       .Characters.Font.Color.RGB = RGB(255, 0, 255)
                       .Characters.Font.Shadow = True
                   End With
                   .Shapes(1).TextFrame.TextRange.Characters.Font.Size = 50
               End With
               rs.MoveNext
           Wend
       End With
   
       ' Run the show.
       ppPres.SlideShowSettings.Run
   
       Exit Sub
   
   err_cmdOLEPowerPoint:
       MsgBox Err.Number & " " & Err.Description
   End Sub
   

9. Save the PowerPointDemo form and open it in Form view.

10. Click PowerPoint Example.

Note that the PowerPoint slide show is created and displayed on your screen. Clicking your mouse moves you through the slides.

This sample shows you how to display slides from PowerPoint on a form in Access. This technique uses Automation in Access to open a PowerPoint presentation and to link to the first slide. Viewing other slides is accomplished by changing the SourceItem property, which enables you to link to different slides.

Note   To use this technique, you must have both PowerPoint and Access installed on your computer. You also need to create a PowerPoint presentation (.ppt). Throughout the procedure, replace the following file name with the name and path of your file: C:\Program Files\Microsoft Office\Office\Pptexample.ppt The sample provided as a download contains a sample presentation.

The following example creates a form with an unbound object frame control and five command buttons for linking to a PowerPoint presentation and for moving through its slides.

To display Microsoft PowerPoint slides on a form, follow these steps:

1. In a new Access database, create a form in Design view.

2. Add the following five controls to the form:

   Command button

   • Name: insertShow
   • Caption: Get Presentation
   • Enabled: Yes

   Command button

   • Name: frstSlide
   • Caption: First Slide
   • Enabled: No

   Command button

   • Name: nextSlide
   • Caption: Next Slide
   • Enabled: No

   Command button

   • Name: previousSlide
   • Caption: Previous Slide
   • Enabled: No

   Command button

   • Name: lastSlide
   • Caption: Last Slide
   • Enabled: No

3. Add an unbound object frame control to the form. In the Insert Object box, click Create New button, select Bitmap Image as the Object Type, and then click OK. Note that the object frame appears as a blank space on the form.

4. Display the property sheet for the unbound object frame, and then set its properties as follows:

   Unbound Object Frame

   • Name: pptFrame
   • SizeMode: Zoom
   • Enabled: Yes
   • Locked: No

5. On the View menu, click Code to open the form module.

6. Add the following code to the General Declarations section:

   Option Explicit
   
   ' Initialize variables.
   Private mcolSlideIDs As Collection
   Private mlngSlideIndex As Long
   

7. In the Object list, click insertShow. In the Procedure list, click Click, and then add the following code:

   Private Sub insertShow_Click()
       On Error GoTo insertShow_Click_Error
   
       ' Open PowerPoint
       Dim strPowerPointFile As String
       Dim pptobj As PowerPoint.Application
       Set pptobj = New PowerPoint.Application
       pptobj.Visible = True
       pptobj.WindowState = ppWindowMinimized
   
       strPowerPointFile = CurrentProject.Path & "\Access2PowerPoint.ppt"
   
       ' Fill a collection with all Slide IDs.
       With pptobj.Presentations.Open(strPowerPointFile)
           Set mcolSlideIDs = New Collection
           Dim ppSlide As PowerPoint.Slide
           For Each ppSlide In .Slides
               mcolSlideIDs.Add ppSlide.SlideID
           Next
           .Close
       End With
   
       ' Close PowerPoint
       pptobj.Quit
       Set pptobj = Nothing
   
       ' Make object frame visible and enable "navigation" buttons.
       pptFrame.Visible = True
       frstSlide.Enabled = True
       lastSlide.Enabled = True
       nextSlide.Enabled = True
       previousSlide.Enabled = True
   
       ' Specify OLE Class, Type, SourceDoc, SourceItem and other properties.
       With pptFrame
           .Class = "Microsoft Powerpoint Slide"
           .OLETypeAllowed = acOLELinked
           .SourceDoc = strPowerPointFile
       End With
       SetSlide 1
   
       frstSlide.SetFocus
       insertShow.Enabled = False
   
       Exit Sub
   
   insertShow_Click_Error:
       MsgBox Err.Number & " " & Err.Description
       Exit Sub
   End Sub
   

8. In the Object list, click frstSlide. In the Procedure list, click Click, and then add the following code:

   Private Sub frstSlide_Click()
       SetSlide 1
   End Sub
   

9. In the Object list, click lastSlide. In the Procedure list, click Click, and then add the following code:

   Private Sub lastSlide_Click()
       SetSlide mcolSlideIDs.Count
   End Sub
   

10. In the Object list, click nextSlide. In the Procedure list, click Click, and then add the following code:

    Private Sub nextSlide_Click()
        SetSlide mlngSlideIndex + 1
    End Sub
    

11. In the Object list, click previousSlide. In the Procedure list, click Click, and then add the following code:

    Private Sub previousSlide_Click()
        SetSlide mlngSlideIndex - 1
    End Sub
    

12. Add the following procedure:

    Private Sub SetSlide(ByVal ID As Integer)
        On Error GoTo ErrorHandler
    
        Select Case ID
        Case Is > mcolSlideIDs.Count
            MsgBox "This is the last slide."
        Case 0
            MsgBox "This is the first slide."
        Case Else
            mlngSlideIndex = ID
            With pptFrame
                .SourceItem = mcolSlideIDs(mlngSlideIndex)
                .Action = acOLECreateLink
            End With
        End Select
    
        Exit Sub
    ErrorHandler:
        MsgBox Err.Number & " " & Err.Description
        Exit Sub
    End Sub
    

13. Close and save the form module.

14. Switch the form to Form view, and then click Get Presentation. Next, click the other buttons to move through the presentation.

This article demonstrates using Automation to work with Access and PowerPoint. This gives you the ability to work from inside Access to create a slide presentation and to use an existing PowerPoint presentation inside an Access form. Using techniques such as these allows you to automate tasks that were performed manually in the past and thus, saving time and adding a professional touch to your applications.

More information about these and related subjects discussed in this article can be found in the following articles:

• Microsoft Knowledge Base Article - ACC2000: How to create sample Visual Basic for Applications code that uses Automation to create a PowerPoint 2000 presentation in Access 2000
• Microsoft Knowledge Base Article - ACC2000: How to Display Microsoft PowerPoint Slides on a Form
• Microsoft Knowledge Base Article - ACC2000: How to Create an MS Graph in PowerPoint Using Access

© Microsoft Corporation. All rights reserved.