Presenting Results in PowerPoint

You use slide presentations to communicate information to an audience, and PowerPoint presentations are especially effective because they organize information in screen-size segments that audiences can absorb quickly. When you add the Excel chart to the presentation, you convey data results in an easy-to-understand format.

Create a Presentation

  1. In the Visual Basic Editor, click Module on the Insert menu to create a new code module.

  2. Add the following CreatePowerPointPres procedure:

    3. Sub CreatePowerPointPres()
    Dim appPPT As New PowerPoint.Application
    Dim presEnergy As PowerPoint.Presentation
    Dim sldUsage As PowerPoint.Slide
    Dim sgBottom As Single   
End Sub

    The first declaration defines the variable appPPT as a new instance of the PowerPoint Application object. The use of the keyword New in the declaration statement indicates that the first time you use the variable appPPT in code, you create a new instance. The second and third declarations define a Presentation object and a Slide object, respectively. The final declaration in the CreatePowerPointPres procedure defines the variable sgBottom as a Single data type, which is set to the bottom coordinate of the first slide's title shape.

  3. Add the following Set statements and With…End block after the declarations in the CreatePowerPointPres procedure:

    4. Set presEnergy = appPPT.Presentations.Add
Set sldUsage = presEnergy.Slides.Add(1, ppLayoutTitleOnly)
With sldUsage.Shapes.Placeholders(1)
    With .TextFrame.TextRange
        .Text = "Energy Usage - " & g_sCircuit
        .Font.Bold = True
        .ChangeCase ppCaseUpper
    End With
    sgBottom = .Top + .Height
End With

    You add the new presentation to the PowerPoint Application object in the first Set statement, and you add a new slide to the new presentation in the second Set statement. The new slide has the Title Only layout, which contains only a Title placeholder and no other preset shapes. The With…End block following the two Set statements accesses the properties of the Title placeholder to set the text, make the font bold, and change the case to uppercase. The last line in the With…End block sets the variable sgBottom to the sum of the top coordinate of the Title placeholder plus its height.

  4. After the With…End block you added in the previous step, add the following With…End block. This will add the Excel chart to the first slide in the presentation:

    5. g_chtUsage.ChartArea.Copy
With sldUsage.Shapes.Paste
    .Top = sgBottom + 20
    .Height = sldUsage.Master.Height - sgBottom + 20
    .Left = sldUsage.Master.Width / 2 - .Width / 2
End With

    In order to add the Excel chart to the PowerPoint presentation, you copy the chart area to the Clipboard and paste it into the Shapes collection of the first slide in the presentation. The With…End block then sets the top and left coordinates and the height of the newly pasted chart shape.

  5. After the With…End block, above End Sub, add the following two lines to save the presentation with the filename "EnergyPres," and to display the PowerPoint application window:

    6. presEnergy.SaveAs g_sDBProjectPath & "EnergyPres"
appPPT.Visible = True

  6. In the Properties window in the Visual Basic Editor, change the Name property of the code module to modPowerPoint.

  7. Click the Save button on the Standard toolbar in the Visual Basic Editor to save changes to the code.

    8. You've now added all the code needed to create a PowerPoint presentation.

Test Your Code

  1. Double-click the modMain project item in the Project Explorer to make the code window active.

  2. In the modMain code module, add the following procedure.

    3. Sub TestCreatingPowerPointPresentation()
    g_sDBProjectPath = Application.CurrentProject.Path & "\"
    modExcel.CreateExcelSheet
    GetDatabaseInfo bReport:=False, bSheet:=False, _
        bPres:=True, bUpdateForm:=False
    modExcel.CreateChart
    modPowerPoint.CreatePowerPointPres
End Sub

Note the values of the arguments passed to the GetDatabaseInfo procedure. You set the first argument, bReport, to False because a report created in Word is not required. You set the second argument, bSheet, also to False. However, because you set the third argument, bPres, to True, an Excel spreadsheet and chart are created in order to copy the chart to the presentation created in PowerPoint. You set the last argument, bUpdateForm, to False because the Access form is not displayed.

When you run the procedure above, the slide should appear as follows:

Click to view at full size.