Export (0) Print
Expand All
Expand Minimize
36 out of 54 rated this helpful - Rate this topic

Working with PowerPoint Presentations from Access Using Automation

Office 2003
 

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.

Download the OfficeAccess2PowerPointSample.exe sample file. (1099 KB)

Contents

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

Introduction

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.

Creating a PowerPoint Presentation from Access Data

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.

Using Automation to Display a PowerPoint Presentation in a Form

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.

Conclusion

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.

Additional Resources

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

Show:
© 2014 Microsoft. All rights reserved.