9.3. Automating PowerPoint

While I really like PowerPoint for creating presentations, I do not particularly care for its object model. It is difficult to use for a number of reasons. First, the object model mixes constants from both the PowerPoint and Office object models. Also, when you add slides to the presentation, the objects are given names that do not identify them (such as Rectangle 2, Rectangle 5, etc.). However, once you have figured out the names for each slide, it is relatively straight forward to use them. Finally, you have to go through a number of steps for each object on the slide in order to be able to put in the text that you want.

Maybe it is because of the relative complexity or because heavy PowerPoint users might not be as versed in VBA as heavy Excel users, but, whatever the reason, I see a lot of requests for getting information like graphs, Excel worksheets, etc. into PowerPoint presentations automatically. So, the example that I present here puts each Excel chart from an Excel workbook on a new slide. For simplicity, I use the name of the chart as the title of the slide.

There is an important distinction in Excel between a Chart and a ChartObject. A chart is a type of sheet in Excel and is part of the Sheets collection, which contains both worksheets and charts. There are also collections for Worksheets and Charts. When you create a chart in Excel, it gives you an option at the end to create the chart as an object in the worksheet or as a separate sheet. For this example, assume that all of the charts are separate chart sheets.

You may wonder why I use Access to automate both Excel and PowerPoint but don't really do anything with Access; I could just as easily use Excel to perform the automation. I chose to use Access because most of the time when I do this, I create the charts in Excel through automation from Access data. So, in a real-world example, I would have a module that creates the charts in Excel, saves the workbook, and then either automates PowerPoint while the Excel workbook is already open or calls a procedure passing the Excel workbook's name as a parameter to perform the PowerPoint automation.

Look at Example 9-5. To try this example, go into a new module in Access and set references to Excel, PowerPoint, and the Microsoft Office Object Libraries.

Example 9-5. Example of how to automate production of a PowerPoint presentation with Excel objects

 Public Sub pptgraphs(  ) Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlchrtsht As Excel.Chart Dim xlsht As Excel.Worksheet Dim xlrng as Excel.Range Dim pptapp As PowerPoint.Application Dim pptpres As PowerPoint.Presentation Dim pptslide As PowerPoint.Slide Dim pptshapes As PowerPoint.Shape Dim x As Integer Set xlapp = New Excel.Application xlapp.Visible = True Set xlwb = xlapp.Workbooks.Open _   ("C:\BookInformation\Chapter9\ExcelCharts.xls") Set pptapp = New PowerPoint.Application pptapp.Visible = Office.msoTrue Set pptpres = pptapp.Presentations.Add x = 1 For Each xlchrtsht In xlwb.Charts   Set pptslide = pptpres.Slides.Add(x, PowerPoint.ppLayoutObject)   pptapp.ActiveWindow.View.GotoSlide (pptslide.SlideIndex)   Set pptshapes = pptslide.Shapes("Rectangle 3")   xlchrtsht.Activate   xlchrtsht.ChartArea.Copy   pptshapes.Select   pptapp.ActiveWindow.View.Paste   Set pptshapes = pptslide.Shapes("Rectangle 2")   pptshapes.TextFrame.TextRange.Text = xlchrtsht.Name   x = x + 1 Next xlchrtsht Set xlsht = xlwb.Sheets("Sheet1") xlsht.Activate Set xlrng = xlsht.Range(xlsht.Cells(1, 1), _     xlsht.Cells.SpecialCells(Excel.xlCellTypeLastCell)) xlrng.Copy Set pptslide = pptpres.Slides.Add(x, PowerPoint.ppLayoutObject) pptapp.ActiveWindow.View.GotoSlide (pptslide.SlideIndex) Set pptshapes = pptslide.Shapes("Rectangle 3") pptshapes.Select pptapp.ActiveWindow.View.PasteSpecial PowerPoint.ppPasteOLEObject, _       Office.msoFalse Set pptshapes = pptslide.Shapes("Rectangle 2") pptshapes.TextFrame.TextRange.Text = "Excel Data" xlwb.Close False Set xlrng = Nothing Set xlchrtsht = Nothing Set xlsht = Nothing xlapp.Quit Set xlapp = Nothing Set pptshapes = Nothing Set pptpres = Nothing Set pptapp = Nothing End Sub

You may get an error message when referencing the PowerPoint shapes collections by specific names, which, in the previous example, are Rectangle 2 and Rectangle 3. Because you probably don't know what it defaults to on your PC and version of Microsoft Office, place the following three lines of code before the line with the error. The first value in your Immediate Window is the Title box, and the second is the Object box.

     For Each pptshapes In pptapp.ActiveWindow.Selection.SlideRange.Shapes         Debug.Print pptshapes.Name       Next

When I tested this code, on one machine it was Rectangle 3 for the Object box, and on another it was Rectangle 5. In any case, this error is relatively easy to fix. Also, since you know that the type of slide you are using has only two boxes, you can iterate through the slides collection, increment a variable by 1 each time, and have the code run that way, which would work regardless of how it was numbered.

There are some items of note in the code. First, when the graph is pasted into the PowerPoint, it is pasted as an embedded Excel workbook. You can also use the following line of code to paste it as a bitmap picture:

     pptapp.ActiveWindow.View.PasteSpecial ppPasteBitmap

If you paste it as a bitmap, you can't edit the graph later by double-clicking on it and going into Excel. If you do not want or need that functionality, then pasting it as a picture is a good idea.

Also, notice that when you type in the code to make the Excel and PowerPoint applications visible, different options come up. The Excel application has only True and False, and they are the simply the Boolean values, but the options for the PowerPoint application to be visible are part of the Office Object's msoTriState enumeration. You'll also notice the same options when you do the PasteSpecial for the Excel range into the presentation.

In addition, take note that each time that I copy something, I make sure that the worksheet, chart, or slide is the object in the respective application. If you omit these lines, you might get errors (it will depend on whether, by chance, the object you copy happens to be the active object). Finally, as the code iterates through the Charts collection in Excel, it adds 1 to the variable x because as we add a new slide, we must tell PowerPoint where to place it. By incrementing a variable by 1 each time that a slide is created, we can easily do this. When you add the slide, you must choose a slide layout. The choices come up when you type the code. I suggest making your presentation the first time from scratch and using the Macro recorder to make sure that you are selecting the correct types.

This code is particularly useful if you keep an Excel workbook of key statistics for a company or department that is put into a presentation each month. Let's assume you have 20 graphs and put all of them into a presentation each month. Tunning this code could create your entire presentation in minutes. You could tweak the code to put the correct titles on the slides or even put on a title slide. If you run into trouble writing your code, say changing font size, you can also record a Macro in PowerPoint and review the code.