any power users build presentations using data from Excel or other data sources. This article shows how to automate Microsoft PowerPoint 2003 from within a Visual Studio 2005 application. The article presents a class called GenPPT, which creates several different types of slides, including slides that integrate tables and charts. GenPPT is written in Visual Basic 2005, and the demo program that calls it is written in C#: this demonstrates using multiple .NET languages in a solution.
Several times in the last year, I’ve seen online questions about generating PowerPoint output automatically. Just like Microsoft Word and Microsoft Excel, PowerPoint contains a rich object model that a developer can utilize to generate attractive and appealing slideshows. So I decided to devote a Baker’s Dozen article to the topic of PowerPoint automation within Visual Studio 2005.
If you’re not sure how to perform a specific programming task in PowerPoint (or any Microsoft Office Product), create a macro and then perform the task. In most instances, the code in the VBA macro will show you the way.
This article presents a Visual Basic 2005 class that aids a developer with the following PowerPoint tasks:
- General structure of GenPPT
- Creating an instance of PowerPoint
- Starting a new presentation with a template and a title page
- Creating a text-only slide with a title and a set of bullet points
- Baker’s Dozen Spotlight: building an Excel table that you can use to generate a PowerPoint table or a Chart object
- Creating a slide that displays an Excel table
- Building an Excel table that you can use as a source for an Excel chart object
- Customizing the display of an Excel chart object
- Creating a slide that displays both a table and a pie chart
- Creating a slide that displays a line chart
- Setting animations and slide transitions
- Defining page footer information
- Saving and displaying the presentation
Deciding on an example to illustrate a set of classes can be an interesting thought process. Initially I was going to use data from the Northwind database?but since it’s near the end of the football season (I’m writing this in mid-December), I decided to have some fun with it. I am a big Kansas City Chiefs fan, and it was only a few years ago that KC started the season 9-0 and had Super Bowl hopes. Of course, it didn’t turn out that way, but I decided to build a PowerPoint presentation on their 2003 season. Here we go!
Tip 1: General Information for GenPPT
GenPPT is a DLL written in Visual Basic 2005. It contains COM references to the Microsoft Excel 11.0 and Microsoft PowerPoint 11.0 Object Libraries. It also contains COM references to Microsoft Core Office Library and Visual Basic for Application Extensibility: Visual Studio 2005 automatically adds these references when you add the PowerPoint and Excel object libraries.
To add these libraries manually, bring up the Solution Explorer window (from the View menu choose Solution Explorer), navigate to the References folder, right-click and choose “Add Reference.” From the Add Reference window, click the COM tab and scroll down to the Microsoft Excel and Microsoft PowerPoint Object Libraries to select them.
GenPPT contains one class file, PowerPointTools.vb. The class contains the following import statements to access the PowerPoint and Excel object models.
Imports PowerPoint?=?_ Microsoft.Office.Interop.PowerPoint Imports Graph = Microsoft.Office.Interop.Graph Imports Excel = Microsoft.Office.Interop.Excel Imports System.Drawing
Table 1 and Table 2 list all the public properties and methods of GenPPT. GenPPT also contains a few support methods for common tasks.
Table 1: GenPPT public properties.
|oPPTApp||Object reference to the PowerPoint application|
|oExcel||Object reference to the Excel application|
|SlideNumber||Current slide number|
|oSheet||Object reference to the current Excel worksheet|
Table 2: Primary GenPPT public methods.
|LaunchPPT||Creates an instance of Powerpoint and Excel|
|SetTemplate||Defines the default template to be used|
|SetFooter||Defines the footer of the PPT|
|BuildTitlePage||Builds the PPT title page slide|
|BuildBulletPage||Builds a slide of bullet points, using a DataTable source|
|BuildTablePage||Builds a slide with a table, using a DataTable source|
|BuidTableChartPage||Builds a slide with a table and chart, using DataTable sources|
|BuildChartPage||Builds a slide with a chart, using a DataTable source|
|SavePPT||Saves the generated PPT|
|DisplayPPT||Displays the generated PPT|
|AddPicture||Add a picture to be centered on the current slide|
|BuildFooter||Build a footer|
|SetSlideTransitions||Sets the slide-to-slide transition effect|
Tip 2: Creating an Instance of PowerPoint
Kicking off GenPPT is easy; just add a reference to PowerPointTools.dll to your project and then run the following code:
// Create instance of?GenPPT PowerPointTools oPPT = new PowerPointTools(); oPPT.LaunchPPT();
The code for LaunchPPT creates new instances for PowerPoint 2003 and Excel. Note that the following method utilizes the object property references to oPPTApp and oExcel, which other methods in the class will also use:
Public Sub LaunchPPT() Me.oPPTApp = New PowerPoint.Application Me.oPPTApp.Visible = False Me.oExcel = New Excel.Application Me.oExcel.Visible = False Me.SlideNumber =?0 End Sub
Tip 3: Starting a New Presentation with a Template and a Title Page
After creating an instance of PowerPoint, you can create a new presentation and specify the template and a title page, using three public methods from GenPPT:
oPPT.SetTemplate("C:\MyTemplates\star.pot"); oPPT.BuildTitlePage("2003 Kansas City Chiefs", _ "By Kevin S. Goff"); oPPT.AddPicture("C:\KCchiefs.gif");
First, the GenPPT code for SetTemplate provides your first exposure to the PowerPoint object model:
Public Sub SetTemplate(ByVal TemplateFilename As String) Me.oPPTPres = Me.oPPTApp.Presentations.Add Me.AddSlide(PowerPoint.PpSlideLayout.ppLayoutTitle) Me.oPPTPres.ApplyTemplate(TemplateFilename)
SetTemplate adds a new presentation and stores an object reference to oPPTPres. The method also calls one of GenPPT’s support methods, AddSlide:
Public Sub AddSlide(ByVal oLayout As PowerPoint.PpSlideLayout) ' Increment the active slide number, ' add a new slide based on that number/layout, ' and go to the slide Me.SlideNumber = Me.SlideNumber + 1 oPPTApp.ActivePresentation.Slides.Add( _ Me.SlideNumber, oLayout) oPPTApp.ActiveWindow.View.GotoSlide(Me.SlideNumber) End Sub
Second, the GenPPT code for BuildTitlePage utilizes another internal method called AddText to place text inside predefined shapes for the current slide layout:
Public Sub BuildTitlePage(ByVal MainTitle As String, ByVal SubTitleTemplate As String) Me.AddText("Rectangle 2", MainTitle) Me.AddText("Rectangle 3", SubTitleTemplate) End Sub
Finally, GenPPT contains the AddPicture method, which displays an image on the center of the current slide:
Public Sub AddPicture(ByVal PicFile As String) Dim oBitmap As Bitmap oBitmap = New Bitmap(PicFile)
Dim PicWidth As Int32 = oBitmap.Width Dim PicHeight As Int32 = oBitmap.Height Dim StartTop As Int32 = (540 - PicHeight) / 2 Dim StartWidth As Int32 = (720 - PicWidth) / 2 Me.oPPTApp.ActiveWindow.Selection.SlideRange. Shapes.AddPicture(PicFile, False, True, _ StartWidth, StartTop) End Sub
? Figure 1. The opening slide (uses layout style).
So far, this leads to the generation of the first title slide, in Figure 1.
As you may have gathered, GenPPT contains wrapper functions so that a developer doesn’t have to access the PowerPoint object model directly. However, you may find a time when you need to access the PowerPoint object reference (Table 1) directly. If so, you’ll need to add a COM reference to the PowerPoint Object Model Library. I’ll show an example of doing this later in the article.
Tip 4: Creating a Text-Only Slide with a Title and a Set of Bullet Points
The next step is to create a table of contents slide with a title at the top and bullet points in the body of the slide. GenPPT must account for a variable-sized list of bullet points, and that some may be indented.
GenPPT provides a method called BuildBulletPage for generating individual slides with bullet point content (see Figure 2). To use this method, create a DataTable with two columns: one for the bullet point text, and an integer column to represent the level of indentation (use 1 for a main bullet point, and increment for each level of indentation when needed).
|Figure 2: The opening slide (generating bullet points).|
DataTable DtBullets = new DataTable(); DtBullets.Columns.Add("Bullets", typeof(String)); DtBullets.Columns.Add("Indent", typeof(Int32)); DtBullets.Rows.Add("Final AFC West Standings",1); DtBullets.Rows.Add("Offensive Leaders", 1); DtBullets.Rows.Add("Receptions by Receiver", 2); DtBullets.Rows.Add("Receptions by Receiver", 2); DtBullets.Rows.Add("Rushing Yardage Chart", 2); DtBullets.Rows.Add("Division QB Ratings", 2); DtBullets.Rows.Add("Margin of Victory Chart ",1); DtBullets.Rows.Add("Game Photos", 1); oPPT.BuildBulletPage("Table of Contents", DtBullets);
The BuildBulletPage method in GenPPT shown below uses the ppLayoutText slide layout to build a bullet page. The method adds a new slide, sets the slide title, and scans through the DataTable. The method examines the Indent column to set the IndentLevel for each bullet point:
Public Sub BuildBulletPage(ByVal MainTitle As String, _ ByVal DtBulletPoints As DataTable) ' Note the indentlevel property, from the IndentLevel in the ' datatable source Me.AddSlide(PowerPoint.PpSlideLayout.ppLayoutText) Me.AddText("Rectangle 2", MainTitle) Me.oPPTApp.ActiveWindow.Selection.SlideRange.Shapes( _ "Rectangle 3").Select() Dim TextCounter As Int32 = 0 For Each Dr As DataRow In DtBulletPoints.Rows oPPTApp.ActiveWindow.Selection.TextRange.InsertAfter( Dr(0) + Chr(13)) TextCounter += 1 oPPTApp.ActiveWindow.Selection.TextRange.Paragraphs( TextCounter, 1).IndentLevel = (Dr(1)) Next End Sub
Tip 5: Baker’s Dozen Spotlight: Building an Excel Spreadsheet to Generate a PowerPoint Table or a Chart Object
Often, creating/generating a PowerPoint slide also involves creating an Excel table. It may be a temporary table, where the contents are pasted into a PowerPoint table or used to create an Excel Chart Object (also pasted into PowerPoint). The next few tips will cover examples of this. The contents may be all numeric entries or a combination of entries, and they may or may not include column headings and alignment definitions.
GenPPT includes a method called BuildExcelTable to generate a temporary Excel table (see Listing 1). This method receives two parameters: a DataTable representing the rows/columns (DtTableEntries), and an optional DataTable containing column headings and alignment definitions (DtHeadings). BuildExcelTable performs the following:
- Adds a new workbook to the oExcel application object
- Writes out column heading lines and sets the alignment for each column (using DtHeadings)
- Writes out the actual data (by scanning through DtTableEntries)
- Selects the entire range of cells written out, and copies them to the system clipboard (for subsequent pasting into PowerPoint)
- Returns a string representing the range of cells (“A1: D10”)
Note that Listing 1 also references a method called WriteExcelCell. As the name implies, the method writes out a value for a particular cell. Calling methods use it when iterating through row/column objects. The next tip will present some code to utilize this capability.
|Figure 3: Generating a table.|
Tip 6: Creating a Slide that Displays an Excel Table
Now that you have a class to build a table, you can provide some data to populate it. Listing 2 and Listing 3 show examples of calling GenPPT with data to build a table that will result in the third slide (see Figure 3).
Tip 7: Building an Excel Table that Can Be Used as a Source of a Excel Chart Object
Now that I’ve covered bullet points and tables, let’s have some fun and throw charts into the mix. Using the same manner that provided data to GenPPT to generate a table, you can do the same thing to generate charts.
|Figure 4: Generating a table and a chart.|
Suppose you define a DataTable and pass it to GenPPT; GenPPT can use the method to build a temporary Excel table and then use the range of cells to generate an Excel chart object. Once GenPPT generates the chart object, it can programmatically copy the chart image to the Windows clipboard and then paste it into a slide that utilizes a chart.
This time I’ll work backwards (“begin with the end in mind”). Figure 4 contains the slide you’ll want to create: a table on the left and an exploded pie chart on the right.
Listing 4 shows how to call GenPPT to generate this output. The following two tips cover the actual GenPPT methods to create the chart.
Tip 8: Customizing the Display of an Excel Chart Object
Listing 5 demonstrates how to create an Excel chart, using the method BuildExcelPieChart. Building a pie chart (and for the most part, building any chart) involves the following:
- Calling BuildExcelTable with a DataTable containing the data for the chart
- Adding a new chart
- Using either ActiveChart.ChartType or ApplyCustomType to define the type of chart. IntelliSense will provide the list of available chart types.
- Setting the source data for the active chart using the cell range that BuildExcelTable returns.
- Setting other properties relevant to the chart type (Legend, X and Y axis, etc.)
- Setting the ColorIndex and LineStyle of the chart’s ChartArea and PlotArea. This is very important to the overall display of the chart.
- Selecting the entire chart area and copying it to the Windows clipboard
Tip 9: Creating a Slide that Displays Both a Table and a Pie Chart
This solution demonstrates how C# and Visual Basic can be used together in Visual Studio 2005. Both languages and associated development environments have their respective strengths.
- Creates a new slide based on the ppLayoutTextAndChart layout.
- Adds a slide title
- Calls BuildExcelTable for the table data, and pastes the results into the table section of the slide.
- Calls BuildExcelPieChart for the chart data, and pastes the results into the chart section of the slide.
- Set other properties relevant to the chart type (Legend, X and Y axis, etc.)
|Figure 5: Generating a line chart.|
Tip 10: Creating a Slide that Displays a Line Chart
Now that you’ve displayed a chart as part of a slide, you’ll now learn to create a chart that occupies the entire slide. Figure 5 shows a line chart that plots the weekly quarterback rating of Chiefs’ quarterback Trent Green through the 2003 and 2004 seasons.
Once again, you’ll create data to drive the table (see Listing 7), and then call a method to build the Line Chart page (see Listing 8). Similar to Tip 9, this method will create a new slide (this time based on the ppLayoutChart layout), and will call the method code in Listing 9 to construct the line chart. Again, similar to the pie chart described in Tip 8, the code builds a chart image based on a chart type and source data.
Tip 11: Setting Animations and Slide Transitions
Of course, no fancy PowerPoint presentation is complete without some type of slide transition. The code below shows a method that sets a slide-by-slide transition of vertical blinds.
Public Sub SetSlideTransitions() ' Basic demonstration of looping through the slideshow collection ' use this as a guide to change settings at the slide level For Each oSlide As PowerPoint.Slide In Me.oPPTApp.ActivePresentation.Slides oSlide.SlideShowTransition.EntryEffect = _ PowerPoint.PpEntryEffect.ppEffectBlindsVertical Next End Sub
|Figure 6: Using IntelliSense in Visual Studio 2005.|
PowerPoint offers a number of slide transitions and animation schemes?the preceding code is just one brief example. Figure 6 illustrates the value of IntelliSense to view the different available options.
The value of the code in this tip isn’t so much the specific result as much as it is a basic demonstration of iterating through a collection of PowerPoint objects.
Tip 12: Defining Slide Footer Information
GenPPT provides a method called BuildFooter (see Listing 10), so that a developer can define footer text for each slide. The method uses the ActivePresentation.SlideMaster.HeaderFooters object hierarchy.
Tip 13: Saving the Presentation
Finally, GenPPT provides a simple method to save a presentation:
The method code for SavePresentation is just as simple:
Public Sub SavePresentation(ByVal PPTName As String) Me.oPPTApp.ActivePresentation.SaveAs(PPTName) End Sub
An excellent book on the subject of Office Automation is Microsoft Office Automation with Visual FoxPro, by Tamar Granor and Della Martin. This is an outstanding reference with many “how-to” examples. Even if you don’t use Visual FoxPro, you can still apply the information in this text to different development languages.
The Baker’s Dozen Commentary:
Until recently, almost all of my development efforts have been in C#. For many projects, C# will continue to be my language of choice. Having said that, I’ve gained some insight into the value of VB, especially when using Visual Studio 2005. The productivity enhancements in the development environment helped me tremendously in building GenPPT.
GenPPT serves as a set of starter classes to help developers with PowerPoint automation. As I look back, there are many opportunities for improvement.
- You could replace the references to specific shape objects (“Rectangle 2”) with something more elegant, such as reading through a shape collection.
- You could replace all the different methods for creating charts with one generic method that uses a chart type as a parameter.
- You might prefer to pass objects instead of datasets.
- You could define fonts and colors as parameters to further customize the display of the output
Finally, there are several places where additional enumerations would improve the readability of the code.
While I’ll continue to modify this project, I encourage people to dive in and take a stab at some of these, or others. Fortunately, end users are never locked into the generated output: they can always modify the output slides.