Working with PowerPoint Presentations from Access Using Automation
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 the OfficeAccess2PowerPointSample.exe sample file. (1099 KB)|
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:
- Start Access and open any database.
- In Design view, create the following form not based on any table or query, with the control indicated:
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:
- 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.
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:
- Name: insertShow
- Caption: Get Presentation
- Enabled: Yes
- Name: frstSlide
- Caption: First Slide
- Enabled: No
- Name: nextSlide
- Caption: Next Slide
- Enabled: No
- Name: previousSlide
- Caption: Previous Slide
- Enabled: No
- 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.
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