Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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 serverso
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:

1. Import from
   

   12-07.MDB the tblParagraphs, tblSlides,
   tlkpLayouts, and tlkpOptions tables.

2. 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).

3. Import the basCommonFile, basGetTemplate, basPowerPoint, and
   basRegistry modules.

4. 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

5. 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.

6. 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).

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

8. 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

9. 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

10. 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).

11. 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:

1. Retrieve the list of pertinent paragraphs from
   tblParagraphs.

2. Loop through all the rows, adding a paragraph to the specified object
   for each.

3. 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.