Working with PowerPoint Presentations from Access Using Automation

Article
09/18/2009

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)

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:

Start Access and open any database.
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"
On the View menu, click Code.
On the Tools menu, click References.
In the Available References box, click Microsoft PowerPoint 9.0 Object Library and Microsoft Office 9.0 Object Library.
Click OK to close the References dialog box.
Add the following line of code to the General Declarations section:
```
Option Explicit
```

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

Save the PowerPointDemo form and open it in Form view.
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:

In a new Access database, create a form in Design view.
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
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.
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
On the View menu, click Code to open the form module.

Add the following code to the General Declarations section:

Option Explicit

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

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

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
```
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
```
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
```
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
```

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

Close and save the form module.
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: