Recipe 12.7 Create a PowerPoint Presentation from Access Data

12.7.1 Problem

You need to create similar Microsoft PowerPoint presentations over and over. You currently take an existing presentation, copy it to a new location, and modify it as necessary, resulting in a number of copies of the same text littering your hard disk. It seems that you could just store all the text and its formatting information in an Access table and then create the presentation programmatically when necessary. Then, you could choose just the slides you need, make modifications as necessary, and have only one place where you store the data. Is this possible?

12.7.2 Solution

Microsoft PowerPoint (part of Microsoft Office) offers an amazingly rich set of objects, methods, and properties. Even though it's not a developer's tool, its object model is spectacularly deep, especially in comparison to Access's. It appears that you can do anything programmatically from an Automation client (such as Access) that you can do manually, using PowerPoint as an Automation server?so the answer to the original question is "Yes!" You can definitely create presentations programmatically from Access using tables to store all the information about your presentation.

This solution involves two major activities: setting up the data in tables and using the interface to create your presentation. This section demonstrates both activities.

To try out the sample application, load and run frmPowerPoint from 12-07.MDB. First choose a template from the combo box's list of templates; then enter a filename to which to save your presentation (click on the "..." button to use the common File Open/Save dialog). Click the Create Presentation button to start PowerPoint and create the presentation. Figure 12-12 shows the sample form in action.

Figure 12-12. Use frmPowerPoint to create PowerPoint presentations from within Access

To use this technique to create your own presentations, follow these steps:

Import from 12-07.MDB the tblParagraphs, tblSlides, tlkpLayouts, and tlkpOptions tables.
Import the frmPowerPoint, zfrmParagraphs, and zsfrmSlides forms (the last two are for setting up your slides only and are not part of the sample's user interface).
Import the basCommonFile, basGetTemplate, basPowerPoint, and basRegistry modules.
Open one of the modules in design mode and choose the Tools References... menu item. For the code to work, your database must include an explicit reference to the DAO and PowerPoint type libraries. Find the options labeled Microsoft DAO Type Library and PowerPoint Object Library (select the most current version of each product), and make sure they're both checked. Figure 12-13 shows the References dialog as it might appear on your machine once you've found and selected the references.

Figure 12-13. Use the Tools > References... dialog to add library references

Open the basGetTemplate module. Modify the first constant (conTemplates) so that it reflects the version of PowerPoint you have installed. The sample is configured for Office 2003; if you're using Office XP, change the "11.0" in the string to "10.0"; if using Office 2000, change it to "9.0".

You can skip Steps 1 through 5 if you want to use 12-07.MDB as it is.

Plan your presentation carefully. You may want to play around in PowerPoint for a while, browsing the slide layouts, before you begin adding data to tables. Or you may want to take an existing presentation and enter it into Access (this is how we originally created this example set of data).
Delete all the rows from tblSlides and tblParagraphs, the two tables containing the presentation information (you may want to make copies of the originals first, in case you need to refer back to them). Leave the two tables whose names start with "tlkp" alone: these tables are necessary for the application to run and contain information about enumerations provided by the PowerPoint object model.
Using zsfrmSlides or editing the table directly, add one row to tblSlides for each slide in your presentation. The SlideNumber field is used for sorting the slides in the presentation (you can enter them in the table in any order you like, but make sure the SlideNumber field reflects the desired output order). The SlideLayout field tells PowerPoint which of its layouts you want to use for the slide: choose its value from the combo box, which pulls its values from tlkpLayouts. It may take some experimentation to find the layout you want. The Include field tells the application whether or not to create a slide in PowerPoint; this way, you can create all your slides in Access but export only selected slides to PowerPoint. Figure 12-14 shows zsfrmSlides gathering slide information.

Figure 12-14. Use zfrmSlides to add new slides to your presentation

Using zsfrmParagraphs or editing the table directly, add one row to tblParagraphs for each paragraph on each slide in your presentation. Table 12-10 lists the fields with comments about each. This table is linked to tblSlides on the SlideNumber field and should include one row for each output paragraph that you need. The three fields, SlideNumber, ObjectNumber, and ParagraphNumber, together make up the primary key; the combination of the three must be unique (none of these fields can be left blank for a given paragraph). Figure 12-15 shows zsfrmParagraphs gathering paragraph information.

Figure 12-15. Use zsfrmParagraphs to add or edit paragraph text and properties

Table 12-10. Field values allowed in tblParagraphs
Field	Values	Description
SlideNumber	Any valid slide number.	Slide number for this paragraph.
ObjectNumber	Any valid object number, depending on the slide layout. This example app does not support adding new objects.	Object number on the selected slide. All text boxes and other items count as objects.
ParagraphNumber	A contiguous, incrementing number, based on previous paragraphs in the selected object.	Paragraph within the object.
IndentLevel	An integer between 1 (no indent) and 5.	Number of levels to indent this paragraph.
Text	Any text, up to a reasonable length (six or sevenwords).	Text for the selected paragraph.
FontName	Any valid installed font. Leave blank to use the default font for the style you've selected.	Name of the font for this paragraph.
FontSize	Any valid font size (1 to 127). 0 indicates that you want to use the default font size for the style you've selected	Font size for this paragraph.
Color	Numeric value representing the color you want to use for your paragraph. 0 indicates that you want to use the default color for the style you've selected.	Color for this paragraph.
Shadow	Select from Yes (-1), No (0), or Use Slide Default (1).	Shadow for this paragraph?
Bold	Select from Yes (-1), No (0), or Use Slide Default (1).	Make this paragraph bold?
Italic	Select from Yes (-1), No (0), or Use Slide Default (1).	Make this paragraph italicized?
Underline	Select from Yes (-1), No (0), or Use Slide Default (1).	Underline this paragraph?
Bullet	Select from the values provided in the lookup table, tlkpBulletTypes.	Type of bullet to use.

Before creating your presentation, peruse the data in tblSlides, making sure that the Include field is set the way you want it (i.e., to include or exclude each slide).
Using frmPowerPoint as previously described, create your presentation in PowerPoint.

12.7.3 Discussion

Creating the presentation boils down to four basic steps:

Start PowerPoint (and shut it down once you're finished).
Create the presentation.
Loop through tblSlides, creating the slides one at a time.
For each slide, loop through the appropriate rows of tblParagraphs, placing and formatting text.

You'll find all the necessary code in basPowerPoint in 12-07.MDB. The following sections describe in detail how these steps work.

12.7.3.1 Starting and stopping PowerPoint

To create the presentation, you must first retrieve a reference to the PowerPoint Application object. If PowerPoint is already running, the GetObject function will be able to retrieve the object reference. If not, the code will jump to an error handler, which will try the CreateObject method. Once the procedure has created and saved the slide presentation, if the code started PowerPoint, it will try to close PowerPoint; if not, it will leave the application running. The following skeleton version of the CreatePresentation function (shown later in its entirety) handles the application startup and shutdown:

Public Function CreatePresentation(blnShowIt As Boolean, _
 ByVal varTemplate As Variant, varFileName As Variant)

    Dim app As PowerPoint.Application
    Dim blnAlreadyRunning As Boolean
    
    On Error GoTo HandleErrors
    
    ' Assume that PowerPoint was already running.
    blnAlreadyRunning = True
    
    Set app = GetObject(, "PowerPoint.Application")
   
   ' Do the work, creating the presentation.
    If Not blnAlreadyRunning Then
        app.Quit
    End If
    Set app = Nothing
    
ExitHere:
    Exit Function
    
HandleErrors:
    Select Case Err.Number
        Case conErrCantStart
            Set app = New PowerPoint.Application
            blnAlreadyRunning = False
            Resume Next
            
      ' Handle other errors...
    End Select
    Resume ExitHere
End Function

12.7.3.2 Creating the presentation

To create the presentation, you must add a new presentation to the application's collection of open presentations. To add a new item to the collection, use the Add method of the Presentations collection of the Application object:

' Get a reference to that new presentation.
Set pptPresentation = app.Presentations.Add(WithWindow:=False)

The Add method of the Presentations collection allows you to create the new presentation with or without a window. If you want PowerPoint to be visible while it's creating the presentation, you can set this parameter to True instead of False. However, if it's set to True, the code that creates the slides runs noticeably slower, and you'll have to contend with other user-interface issues (PowerPoint will request confirmation on overwriting existing presentations when you save this one, for example). We suggest leaving this parameter set to False unless you have some overriding reason to change it.

Once you've created the presentation, the code uses the ApplyTemplate method of the new Presentation object, given the name of the template you've chosen from frmPowerPoint:

If Len(varTemplate & "") > 0 Then
   pptPresentation.ApplyTemplate varTemplate
End If

The code then calls the user-defined CreateSlides function, passing to it the new Presentation object, to create all the slides for the presentation.

This section and the previous one draw their code from the CreatePresentation function in basPowerPoint. Here's the function in its entirety:

Public Function CreatePresentation(blnShowIt As Boolean, _
 ByVal varTemplate As Variant, varFileName As Variant)

    ' Highest-level routine. Actually create the
    ' presentation, and set up the slides.
    
    Dim pptPresentation As PowerPoint.Presentation
    Dim lngResult As Long
    Dim app As PowerPoint.Application
    Dim blnAlreadyRunning As Boolean
    
    On Error GoTo HandleErrors
    
    ' Assume that PowerPoint was already running.
    blnAlreadyRunning = True
    
    Set app = GetObject(, "PowerPoint.Application")
    
    ' If the caller wants to see this happening, make the
    ' application window visible and set the focus there.
    If blnShowIt Then
        app.Visible = True
        AppActivate "Microsoft PowerPoint"
    End If
    
    ' Get a reference to that new presentation.
    Set pptPresentation = app.Presentations.Add(WithWindow:=False)
    If Len(varTemplate & "") > 0 Then
        pptPresentation.ApplyTemplate varTemplate
    End If
    
    lngResult = CreateSlides(pptPresentation)
    pptPresentation.SaveAs FileName:=varFileName
    If Not blnAlreadyRunning Then
        app.Quit
    End If
    Set app = Nothing
    
ExitHere:
    Exit Function
    
HandleErrors:
    Select Case Err.Number
        Case conErrCantStart
            Set app = New PowerPoint.Application
            blnAlreadyRunning = False
            Resume Next
            
        Case conErrFileInUse
            MsgBox "The output file name is in use." & vbCrLf & _
             "Switch to PowerPoint and save the file manually.", _
             vbExclamation, "Create Presentation"
        
        Case Else
            MsgBox "Error: " & Err.Description & " (" & Err.Number & ")", _
             vbExclamation, "Create Presentation"
    End Select
    Resume ExitHere
End Function

12.7.3.3 Creating each slide

Once you've created the presentation, the next step is to loop through all the rows in tblSlides, creating the slide described by each row. The code in CreateSlides, shown next, does the work. It boils down to a single line of code: you must call the Add method of the Slides collection for the current presentation to add each slide:

Set objSlide = obj.Slides.Add(intCount, rstSlides("SlideLayout"))

As you can see, you must provide the Add method with the index of the slide you're creating and the layout type for the slide. (See the table tlkpLayouts for all the possible layouts and the associated enumerated value for each.) The CreateSlides function walks through tblSlides one row at a time, creating the slide and calling the user-defined CreateSlideText function for each slide whose Include flag is set to True.

The complete source code for the CreateSlides function is:

Private Function CreateSlides(obj As Presentation)
    ' obj is the PowerPoint presentation object.
    ' It contains slide objects.
    
    Const acbcDataSource = "qrySlideInfo"
    
    Dim rstSlides As DAO.Recordset
    Dim db As DAO.Database
    Dim objSlide As PowerPoint.Slide
    
    Dim intSlide As Integer
    Dim intObject As Integer
    Dim intParagraph As Integer
    Dim intCount As Integer
    Dim strText As String
    Dim blnDone As Boolean
    
    On Error GoTo HandleErrors
    
    Set db = CurrentDb( )
    Set rstSlides = db.OpenRecordset( _
     "Select * from tblSlides Where Include Order By SlideNumber")
    blnDone = False
    Do While Not rstSlides.EOF And Not blnDone
        If rstSlides("Include") Then
            intCount = intCount + 1
            ' Add the next slide.
            Set objSlide = obj.Slides. _
             Add(intCount, rstSlides("SlideLayout"))
            If Not CreateSlideText( _
             objSlide, rstSlides("SlideNumber")) Then
                blnDone = True
            End If
        End If
        rstSlides.MoveNext
    Loop
    
ExitHere:
    If Not rstSlides Is Nothing Then
        rstSlides.Close
    End If
    Exit Function
    
HandleErrors:
    Select Case Err.Number
        Case Else
            MsgBox "Error: " & Err.Description & " (" & Err.Number & ")", _
             vbExclamation, "Create Slides"
                    
    End Select
    Resume ExitHere
End Function

12.7.3.4 Creating the text

Creating the slide text can be broken down into these small steps:

Retrieve the list of pertinent paragraphs from tblParagraphs.
Loop through all the rows, adding a paragraph to the specified object for each.
Loop through the rows again, setting the formatting for each paragraph.

Why loop through the rows for each slide twice? Because of the way PowerPoint handles inserted text, you must first insert the rows, and then go back and format those rows. Otherwise, each new paragraph will "inherit" the formatting of the previous paragraph. To work around this in the simplest manner possible, the code inserts each of the paragraphs and sets the indent and bullet, then makes a second pass through the paragraphs and sets the necessary formatting. Although this may take a bit longer, it simplifies the code.

The following paragraphs describe each step from the CreateSlideText function, which is shown in its entirety later in this section.

To retrieve the list of paragraphs that apply to the current slide, CreateSlides passes the slide object and its index as arguments to CreateSlideText. Given that index, CreateSlideText can request just the paragraphs associated with that slide from tblParagraphs:

Set db = CurrentDb( )

' Go get the text that applies to this slide.
Set rst = db.OpenRecordset("SELECT * FROM tblParagraphs " & _
 "WHERE SlideNumber = " & intSlideNumber & _
 " ORDER BY ObjectNumber, ParagraphNumber")

Call InsertText(rst, objSlide)

The next step is to insert the slides, text, indents, and bullets into the presentation. The InsertText procedure takes care of this task, given a reference to the recordset and to the slide. This code retrieves various fields from the recordset (which contains information for this one slide only), inserts the text it finds in the table into the shape, and then sets the indent level and bullet type based on information from the recordset:

Private Sub InsertText(rst As DAO.Recordset, sld As PowerPoint.Slide)
    Dim pptShape As PowerPoint.Shape
    Dim intParagraph As Integer
    
    Do Until rst.EOF
    ' Insert all the paragraphs and indents, to get them right first.
    ' Then we'll go back and insert the formatting. This is required
    ' because of the way PowerPoint carries fonts forward from one
    ' paragraph to the next when inserting paragraphs.
        
        Set pptShape = sld.Shapes(rst("ObjectNumber"))
        pptShape.TextFrame.TextRange.InsertAfter rst("Text") & vbCrLf
        With pptShape.TextFrame.TextRange. _
         Paragraphs(rst("ParagraphNumber"))
            If Not IsNull(rst("IndentLevel")) Then
                .IndentLevel = rst("IndentLevel")
            End If
            .ParagraphFormat.Bullet.Type = rst("Bullet")
        End With
        rst.MoveNext
    Loop
End Sub

Next, the code in CreateSlideText moves back to the beginning of the recordset and begins a loop that updates the formatting for each paragraph on the slide. For each row in the recordset, CreateSlideText retrieves a reference to the necessary slide object. Each object on the slide that can contain text is numbered, and the recordset contains an index (intObject) indicating which object you want to place your text into. If the value of the index in the recordset does not equal the current object index on the slide, the code retrieves a reference to the correct shape on the slide:

If intObject <> rst("ObjectNumber") Then
    intObject = rst("ObjectNumber")
    Set pptShape = objSlide.Shapes(intObject)
End If

The code then retrieves a reference to the correct paragraph so that it can work with the various properties of that paragraph:

Set pptTextRange = pptShape.TextFrame.TextRange. _
 Paragraphs(rst("ParagraphNumber"))

Next, CreateSlideText sets the formatting properties corresponding to each field in tblParagraphs:

With pptTextRange.Font
    If Not IsNull(rst("FontName")) Then
        .Name = rst("FontName")
    End If
    If rst("FontSize") > 0 Then
        .Size = rst("FontSize")
    End If
    If rst("Color") > 0 Then
        .Color = rst("Color")
    End If
    
    ' Set Yes/No/Use Default properties.
    If rst("Shadow") <> conUseDefault Then
        .Shadow = rst("Shadow")
    End If
    If rst("Bold") <> conUseDefault Then
        .Bold = rst("Bold")
    End If
    If rst("Italic") <> conUseDefault Then
        .Italic = rst("Italic")
    End If
    If rst("Underline") <> conUseDefault Then
        .Underline = rst("Underline")
    End If
End With

Once CreateSlideText has set all the necessary properties, it moves on to the next row. If at any point it encounters an error setting the properties of a given paragraph, it moves on to the next paragraph. (You might consider beefing up this error handling, but for the most part, it works fine.) Here, then, is the complete source for CreateSlideText:

Private Function CreateSlideText( _
 objSlide As PowerPoint.Slide, intSlideNumber As Integer)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim pptShape As PowerPoint.Shape
    Dim intObject As Integer
    Dim intParagraph As Integer
    Dim pptTextRange As PowerPoint.TextRange
    Dim objFormat As PowerPoint.TextEffectFormat
    Dim strFontName As String
    Dim fnt As PowerPoint.Font
            
    On Error GoTo HandleErrors
    
    Set db = CurrentDb( )
    
    ' Go get the text that applies to this slide.
    Set rst = db.OpenRecordset("SELECT * FROM tblParagraphs " & _
     "WHERE SlideNumber = " & intSlideNumber & _
     " ORDER BY ObjectNumber, ParagraphNumber")
     
    ' Now walk through the list of text items, sticking
    ' them into the objects and applying properties.
    
    Call InsertText(rst, objSlide)
   
    rst.MoveFirst
    Do Until rst.EOF
        ' Update the status information on the form.
        With Forms("frmPowerPoint")
            .UpdateDisplay rst("SlideNumber"), rst("Text")
            .Repaint
        End With
    
        ' No need to grab a reference to the shape each
        ' time through. Cache this value for later use.
        If intObject <> rst("ObjectNumber") Then
            intObject = rst("ObjectNumber")
            Set pptShape = objSlide.Shapes(intObject)
        End If
    
        ' Get a reference to the paragraph in question,
        ' then set its paragraph properties.
        Set pptTextRange = pptShape.TextFrame.TextRange. _
         Paragraphs(rst("ParagraphNumber"))
    
        With pptTextRange.Font
            If Not IsNull(rst("FontName")) Then
                .Name = rst("FontName")
            End If
            If rst("FontSize") > 0 Then
                .Size = rst("FontSize")
            End If
            If rst("Color") > 0 Then
                .Color = rst("Color")
            End If
            
            ' Set Yes/No/Use Default properties.
            If rst("Shadow") <> conUseDefault Then
                .Shadow = rst("Shadow")
            End If
            If rst("Bold") <> conUseDefault Then
                .Bold = rst("Bold")
            End If
            If rst("Italic") <> conUseDefault Then
                .Italic = rst("Italic")
            End If
            If rst("Underline") <> conUseDefault Then
                .Underline = rst("Underline")
            End If
        End With

CreateSlideTextNext:
        rst.MoveNext
    Loop
    CreateSlideText = True
    
ExitHere:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    
    Set db = Nothing
    Exit Function
    
HandleErrors:
    CreateSlideText = False
    Select Case Err.Number
        Case conErrInvalidObjectIndex
            Resume CreateSlideTextNext
    
        Case Else
            MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",_
             vbExclamation, "Create Slides Text"
    
    End Select
    Resume ExitHere
End Function

12.7.4 Comments

This solution uses only a small subset of the PowerPoint Automation interface. A great deal more functionality is available to you if you dig deep enough to find it. For example, you might want to support more of the text or bullet attributes than we've chosen, or dig into slide transitions, builds, and animation. Use the Object Browser (press F2 in a module window), shown in Figure 12-16, to help dig through the PowerPoint object model. You can work your way down through the hierarchy in an orderly fashion. For example, find the Application object in the left window, then browse through the right window until you find the Presentations collection. On the left, find the Presentations collection, and on the right, find the Add method. That's how we wrote this solution: by digging through the various objects, collections, methods, and properties that the Object Browser displays.

Figure 12-16. The Object Browser makes it possible to dig around in object models

You may also want to look at basGetTemplate, which includes a substantial amount of code dedicated to retrieving a list of all of PowerPoint's design templates. As it's installed, PowerPoint places the location of these templates in your registry. Two interesting issues are involved here: finding the name of the directory where the templates have been installed, and creating an array containing the names of the templates. Once the code creates the array, it uses the standard list-filling callback function mechanism, described in Chapter 7, to populate the combo box on the sample form. Though these topics are beyond the scope of this solution, you may find it useful to dig into the code, which has comments to help you through it.