John Clarkson
Microsoft Corporation
April 1999
Summary: Shows how to create a Microsoft® PowerPoint® add-in that builds a presentation from scratch by displaying data from a Microsoft Access database query. (11 printed pages) Covers:
Introduction
Creating Add-ins in PowerPoint
Running Add-ins in PowerPoint
The Code in the Sample
Creating a Sample Add-in
Conclusion
This article demonstrates how to create a PowerPoint add-in that adds a Sales Report command to the Tools menu when it's loaded. When the Sales Report command is selected, the add-in uses Microsoft Visual Basic® for Applications (VBA) and Data Access Objects (DAO) code to build from scratch a presentation that displays data from the Northwind sample database. The presentation lists the two top-selling products, with sales figures, for each product category in Northwind.
Add-ins are supplemental programs that extend the capabilities of an application by adding custom commands and specialized features. One example might be an add-in that prompts a salesperson to enter last-minute or customer-specific data into a form just before a sales call, and then uses this data to update a presentation. Another example is an add-in that allows a financial manager to automatically build a presentation summarizing the firm's monthly results.
You can buy add-ins from independent software vendors, you can download them from the Microsoft Office Update Web site (http://officeupdate.microsoft.com/), or you can create your own add-ins. To create a PowerPoint add-in, you create a new presentation and save it as a PowerPoint add-in (.ppa) file.
To create a PowerPoint add-in
You can load an add-in manually or programmatically.
Use this procedure to load PowerPoint add-ins manually.
To load an add-in manually
The add-in is registered, and a check mark appears next to its name. (Note that using Windows Explorer to open a PowerPoint add-in file will also load the add-in.)
To load an add-in programmatically, first add it to the AddIns collection, and then set the Loaded property of the corresponding AddIn object to True. You can perform these two actions in a single step, as shown in the following example. (Note that you use the file name of the add-in, not its title, with the Add method.)
AddIns.Add _
("C:\Program Files\Microsoft " _
& "Office\Office\YourAddIn.ppa") _
.Loaded = True
Select Compile VBA Project on the Tools menu to compile your Visual Basic for Applications code. This detects run-time errors at design time, which you may prefer to waiting until run time.
You can set a password for your PowerPoint add-in project in order to prevent unwanted viewing and editing, but it should not be necessary. Once your PowerPoint project is saved as an add-in and closed, the code is no longer visible.
Loading an add-in won't by itself trigger your code. To automatically run code when your add-in is loaded, create a subprocedure named Auto_Open in a standard module in the add-in project. In the example described later in this article, I use an Auto_Open procedure to add a menu item that the user can use to actually start the add-in itself. (Of course, once an add-in is loaded, you can also use the Macro command on the Tools menu to start add-in code.)
To run code when your add-in is unloaded, create a subprocedure named Auto_Close in a standard module in the add-in project. In the example to follow, I used the Auto_Close procedure to remove the menu item that launches the add-in. This kind of cleanup work is a courtesy to users. Don't leave items hanging around in the environment after your add-in is removed.
The code in the sample add-in is pretty simple. This is what it does:
The code in the Auto_Open and Auto_Close procedures is basically the same code as used in several of the other articles about add-ins in Office 97. For details, "Creating Add-ins in Excel 97" or "Creating Add-ins in Word 97."
Use the Add method of the PowerPoint Presentations collection to open a new presentation.
Set pptPresentation = Presentations.Add(True)
pptPresentation.ApplyTemplate FileName:=strTemplate
In this case, we're basing the presentation on a PowerPoint template (.pot file) that ships with Office 97. We use the ApplyTemplate method to apply the template.
You could also base the presentation on the template by opening it directly. However, if slides are added to the template, you won't get a clean presentation. Also, if the design template doesn't exist on the user's system, or is moved, users will get a "PowerPoint could not open the file" error. Using the ApplyTemplate method avoids the error -- you'll open with a blank presentation.
As you might guess, we'll use the Add method of the Slides collection to add a slide. The key line of code is this one, and it's definitely my favorite in this solution:
.View.GotoSlide Index:=pptSlides.Add(Index:=1, _
Layout:=ppLayoutText).SlideIndex
This statement breaks down into two parts: There's a call to the GotoSlide method, which wraps a call to the Add method. The core is really the Add method call itself, [Add(Index:=1, Layout:=ppLayoutText)], where we specify that the added slide goes first in the presentation sequence, and call for the slide to contain a text layout.
Next, note two things: First, the GotoSlide method requires an Index argument; and second, the Add method call ends by reading the SlideIndex property. These two pieces fit together in a very clever way: The value returned by the SlideIndex property (part of the Add method call!) is passed to the GotoSlide method's Index argument. We've managed to pack two method calls into a single statement! (I can't claim credit for this; it comes from an unnamed support engineer somewhere at Microsoft.)
The remaining code is pretty straightforward. Note that a slide with its layout set to ppLayoutText always starts its life containing two Shape objects with Name properties set to Rectangle 2 and Rectangle 3. Also note how dense the object hierarchy gets at this point—the full path is seven levels deep: "PowerPoint.ActivePresentation.Slides().Shapes().TextFrame.TextRange.Text."
With pptSlides(1)
.Shapes("Rectangle 2").TextFrame.TextRange = "Top Products"
.Shapes("Rectangle 3").TextFrame.TextRange = _
"Top two products in each category, with sales totals."
End With
This is very basic DAO code. The OpenDatabase method points to a Jet database. The OpenRecordset method points to a query name associated with the database, and opens it as a snapshot-type Recordset object. Add a reference to the most current version of the DAO object library (Tools menu, References item) before running this code.
Set dbsNorthwind = OpenDatabase(strDbPath)
Set rsSales = dbsNorthwind.OpenRecordset _
(strQueryName, dbOpenSnapshot)
In this case, the query is one that ships with the Northwind sample database in Office 97, modified to sort the results first by category, and then within category by sales.
The master procedure contains an outer Do...Loop statement that ends when it finds an end of file in the set of records. The loop contains an error handler that checks for a Jet database "no current record" error, which would be triggered by a MoveNext statement occurring at the bottom of the set of records.
We step through the loop once for each product category. At the top of the loop we call the MakeSlide procedure. This procedure contains code very similar to the code that creates the title slide, discussed earlier in the section "Adding a Title Slide." In MakeSlide we read the product category, create a slide, and set the Text property of the TextRange object to add the product category to Rectangle 2 on the slide.
pptSlides(pptSlides.Count). _
Shapes("Rectangle 2").TextFrame.TextRange = strProductCategory
Note that the Text property is the default property of the TextRange object, so it is not necessary to specify it in code.
Next in the Do...Loop structure, a For...Next loop executes twice, calling the GetData procedure, which reads product data and calls the InsertAfter method to add the product data to Rectangle 3 on the slide created in the MakeSlide procedure.
pptSlides(pptSlides.Count).Shapes("Rectangle 3"). _
TextFrame.TextRange.InsertAfter _
(vbCrLf & strProductName & vbTab & strSales)
The final step in the outer Do...Loop is to use an inner Do...Loop to call the MoveNext statement repeatedly until we find a new product category.
Do While strProductCategory = rsSales!categoryName
.
.
.
rsSales.MoveNext
Loop
Once the outer Do...Loop exits, the final step in the master procedure is to release the memory used by the two object variables.
Set dbsNorthwind = Nothing
Set rsSales = Nothing
This section explains how to re-create the Sales Report add-in, providing code and procedures.
Option Explicit
Sub Auto_Open()
' Adds an item to the Tools menu.
' This procedure runs automatically when the add-in loads.
Dim offCmdBrPp As CommandBarPopup
Dim offCmdBtn As CommandBarButton
' Create an object variable referring to the Tools menu.
Set offCmdBrPp = _
Application.CommandBars("Menu Bar").Controls("Tools")
' If the Address Book Report command already exists, delete it.
On Error Resume Next
offCmdBrPp.Controls("Products Slide Show").Delete
' Add a command in the fourth position on the Tools menu.
Set offCmdBtn = _
offCmdBrPp.Controls.Add(Type:=msoControlButton, Before:=4)
' Set properties on the new command.
With offCmdBtn
.Caption = "Products Slide Show"
.OnAction = "PresentationMaster"
End With
End Sub
Sub Auto_Close()
' Deletes an item on the Tools menu.
' This procedure runs automatically when the add-in unloads.
' If the Address Book Report command exists, delete it.
On Error Resume Next
Application.CommandBars _
("Menu Bar").Controls("Tools").Controls _
("Products Slide Show").Delete
End Sub
Sub PresentationMaster()
' Create a presentation and apply "Whirlpool" template.
Dim rsSales As Recordset
Dim dbsNorthwind As Database
Dim strProductCategory As String
Dim strProductName As String
Dim lngLoopCounter As Long
Dim strErrorMessage As String
Dim strTemplate As String
Dim strDbPath As String
Dim strQueryName As String
Dim bolTemplateExists As Boolean
Dim pptSlides As Slides
Dim pptPresentation As Presentation
On Error GoTo PresentationMaster_Err
Set pptPresentation = Presentations.Add(True)
Set pptSlides = pptPresentation.Slides
strTemplate = "C:\Program Files\Microsoft " _
& "Office\Templates\Presentation " _
& "Designs\Whirlpool.pot"
bolTemplateExists = FileExists(strTemplate)
If bolTemplateExists = False Then
strErrorMessage = "The template Whirlpool.pot"
strErrorMessage = strErrorMessage & _
"doesn't exist at 'C:\Program Files\Microsoft"
strErrorMessage = strErrorMessage & _
"Office\Templates\Presentation"
strErrorMessage = strErrorMessage & _
"Designs\Whirlpool.pot"
MsgBox strErrorMessage
Exit Sub
End If
pptPresentation.ApplyTemplate FileName:=strTemplate
With ActiveWindow
'Create a title slide.
.ViewType = ppViewSlide
'Add a new slide and switch to it.
.View.GotoSlide Index:=pptSlides.Add _
(Index:=1, Layout:=ppLayoutText).SlideIndex
End With
With pptSlides(1)
.Shapes("Rectangle 2").TextFrame.TextRange = "Top Products"
.Shapes("Rectangle 3").TextFrame.TextRange = _
"Top two products in each category, with sales totals."
End With
strDbPath = "C:\Program Files\Microsoft " _
& "Office\Office\Samples\Northwind.mdb"
strQueryName = "New Sales by Category"
Set rsSales = _
GetRecordSet(dbsNorthwind, rsSales, strDbPath, strQueryName)
' This loop reads product data from the Northwind
' database and adds it to PowerPoint slides.
rsSales.MoveFirst
Do While Not rsSales.EOF
' MakeSlide creates a new slide.
Call MakeSlide(rsSales, strProductCategory, pptSlides)
' Get two values if the product name is unchanged.
For lngLoopCounter = 0 To 1
If strProductCategory = rsSales!categoryName Then
Call GetData(rsSales, strProductName, pptSlides)
End If
Next lngLoopCounter
'Find the next product category.
On Error Resume Next
Do While strProductCategory = rsSales!categoryName
If Err.Number = 3021 Then
Exit Do
ElseIf Err.Number > 0 Then
On Error GoTo PresentationMaster_Err
End If
rsSales.MoveNext
Loop
Loop
PresentationMaster_End:
Set dbsNorthwind = Nothing
Set rsSales = Nothing
Exit Sub
PresentationMaster_Err:
strErrorMessage = "Error: " & Err.Number & _
" " & Err.Description & vbCrLf
strErrorMessage = strErrorMessage & _
"Unrecognized error in the" & vbCrLf
strErrorMessage = strErrorMessage & _
"PresentationMaster procedure."
MsgBox strErrorMessage
Resume PresentationMaster_End
End Sub
Function GetRecordSet(dbsNorthwind As Database, _
rsSales As Recordset, _
strDbPath As String, _
strQueryName As String) As Recordset
' This procedure opens the database and
' creates the Recordset object that
' contains data for the presentation.
' Called by the ReportMaster procedure.
Dim strErrMessage As String
On Error GoTo GetRecordSet_Err
Set dbsNorthwind = OpenDatabase(strDbPath)
Set rsSales = _
dbsNorthwind.OpenRecordset(strQueryName, dbOpenSnapshot)
Set GetRecordSet = rsSales
GetRecordSet_End:
Exit Function
GetRecordSet_Err:
Select Case Err.Number
Case 3078
MsgBox "Query doesn't exist."
Case 3024
MsgBox "Northwind is not installed at the expected location."
Case Else
strErrMessage = "Error: " & Err.Number & _
" " & Err.Description & vbCrLf
strErrMessage = strErrMessage & _
"Unrecognized error in the" & vbCrLf
strErrMessage = strErrMessage & _
"GetRecordSet procedure."
MsgBox strErrMessage
End Select
Resume GetRecordSet_End
End Function
Function FileExists(strFile As String) As Boolean
Dim strSuccess As String
strSuccess = Dir(strFile)
If Len(strFile) > 0 Then
FileExists = True
Else
FileExists = False
End If
End Function
Sub MakeSlide(rsSales As Recordset, _
strProductCategory As String, _
pptSlides As Slides)
' This procedure creates a new slide and
' adds Product Category as the slide title.
' Called from the PresentationMaster procedure.
' The rsSales parameter is the
' Recordset object created by the Access query.
' The strProductCategory parameter contains
' the current product category.
' Read the product category.
strProductCategory = rsSales!categoryName
ActiveWindow.View.GotoSlide ActivePresentation.Slides.Add _
(Index:=ActivePresentation.Slides. _
Count + 1, Layout:=ppLayoutText).SlideIndex
pptSlides(pptSlides.Count). _
Shapes("Rectangle 2").TextFrame.TextRange = strProductCategory
End Sub
Sub GetData(rsSales As Recordset, _
strProductName As String, _
pptSlides As Slides)
' This procedure reads product data and adds it to the slide.
' Called from PresentationMaster procedure.
' The rsSales parameter is the Recordset object
' created by the Access query.
Dim strSales As String
' Read the product name.
strProductName = rsSales!ProductName
' Read sales data and put it in currency format.
strSales = Format(rsSales!ProductSales, "currency")
pptSlides(pptSlides.Count). _
Shapes("Rectangle 3").TextFrame.TextRange.InsertAfter _
(vbCrLf & strProductName & vbTab & strSales)
rsSales.MoveNext
End Sub
You'll find that PowerPoint add-ins are architecturally very similar to Microsoft Excel add-ins, and that using add-ins to programmatically build periodic reports in PowerPoint is a very effective combination of Visual Basic for Application's automation capabilities and PowerPoint's strengths as a presentation tool. This sample add-in could easily be modified to read data from other data sources and to use more of PowerPoint's formatting features.
This add-in could also be extended by exporting data to Excel for numerical analysis, and by using Microsoft Graph to display charts in the presentation. We'll actually do this in another article later in this add-in series, "Using Microsoft Excel and Microsoft Graph to Extend a PowerPoint Add-in."