Power Point Helper Functions

Our next chore is to create a title slide. The title slide will consist of a title line plus descriptive text, similar to the Word report we created in the last example.

1. Still working on the standard code module with your PowerPoint code, add a new subroutine and name it CreateTitleSlide.

2. Add the following line of code:

Set m_oPptSlide = m_oPptShow.Slides.Add(1, ppLayoutTitle)

The PowerPoint Presentation object contains a Slides collection that naturally contains all of the slides in a presentation file. The Slides collection's Add method adds a slide to the collection and returns a Slide object back. Here we are assigning that new slide to our m_oPptSlide variable.

The Add method takes two parameters. The first is the slide index. This tells PowerPoint where to put the slide. In our code, it's set to 1 since we're creating the first or title slide. The second parameter is the type of auto-layout to use. Figure 8-9 uses the Object Browser to show the many options available.

Figure 8-9. Slide layout enums listed in the Object Browser

The enum we've used represents a layout with a title placeholder and a text placeholder.

3. Add the following code to the CreateTitleSlide subroutine:

With m_oPptSlide.Shapes.Placeholders(1) With .TextFrame.TextRange .Text = GetTitle .Font.Bold = msoTrue .ChangeCase ppCaseUpper End With End With

With m_oPptSlide.Shapes.Placeholders(2) With .TextFrame.TextRange .Text = GetTitleBody .Font.Bold = msoFalse .ChangeCase ppCaseUpper End With End With

Within these two With...End With blocks, we are adding our title text and descriptive text to the Placeholder objects on the title slide. In the first With...End With block, we are setting the title and adding bold formatting to the text. In the second With...End With block, we are adding the title body (or descriptive) text with no bold formatting. The completed CreateTitleSlide subroutine should look like the code in Listing 8-8.

Listing 8-8. Complete CreateTitleSlide Subroutine

Sub CreateTitleSlide() Set m_oPptSlide = m_oPptShow.Slides.Add(1, ppLayoutTitle)

With m_oPptSlide.Shapes.Placeholders(1) With .TextFrame.TextRange .Text = GetTitle .Font.Bold = msoTrue .ChangeCase ppCaseUpper End With End With

With m_oPptSlide.Shapes.Placeholders(2) With .TextFrame.TextRange .Text = GetTitleBody .Font.Bold = msoFalse .ChangeCase ppCaseUpper End With End With End Sub

Next, we'll create a procedure to add the slide charts.

1. Add a new subroutine to the code module we've been working in. Name it CreateChartSlides.

2. Add the following variable declarations to CreateChartSlides:

Dim i As Integer

Dim sTitle As String

Dim sngChartStart As Single

Dim spacer As Integer

The first variable, i, is the counter variable for the loop we'll use when enumerating through our charts. sTitle will store the title text for each chart slide. The sngChartStart variable will be used to help us determine where to place the chart on the slide and how to size it. The last variable, spacer, will be used to put a bit of space between the title placeholder and the chart.

3. Add a For...Next loop with two blank lines between the start and end of the loop, as follows:

Next i

The entire subroutine will take place within this For...Next block.

4. Add the following lines of code:

Worksheets(1).ChartObjects(i).Activate sTitle = ActiveChart.ChartTitle.Text

The first line activates the chart with an index of i. The second line retrieves the title of the chart we just made active.

Next, we'll add a slide to place the chart on.

5. Add the following line of code:

Set m_oPptSlide = m_oPptShow.Slides.Add(i + 1, ppLayoutTitleOnly) This line adds a new slide and gives it a layout that contains only a title placeholder.

6. Add the following code to the CreateChartSlides procedure:

With m_oPptSlide.Shapes.Placeholders(1) sngChartStart = .top + .height

With .TextFrame.TextRange

.Text = sTitle End With End With

In this With...End With block, we are assigning a value to the sngChartStart variable, which is the total of the title placeholder's Top and Height property values. This will be used when we place the chart on the slide. Next, we add the chart title text to the title placeholder.

Next, we'll use the Excel Chart object's Copy method to place the chart in memory, and then we can paste it into the slide and place it in its proper location.

7. Add the following code to the CreateChartSlides procedure:

Worksheets(1).ChartObjects(i).Copy spacer = 20

With m_oPptSlide.Shapes.Paste .top = sngChartStart + spacer

.height = m_oPptSlide.Master.height - sngChartStart + spacer .left = m_oPptSlide.Master.width / 2 - .width / 2 End With

The completed CreateChartSlides subroutine should look like Listing 8-9.

Listing 8-9. Complete CreateChartSlides Subroutine

Sub CreateChartSlides() Dim i As Integer Dim sTitle As String Dim sngChartStart As Single Dim spacer As Integer

For i = 1 To 3 Worksheets(1).ChartObjects(i).Activate sTitle = ActiveChart.GiartTitle.Text

Set m_oPptSlide = m_oPptShow.Slides.Add(i + 1, ppLayoutTitleOnly)

With m_oPptSlide.Shapes.Placeholders(1) sngChartStart = .top + .height

With .TextFrame.TextRange

.Text = sTitle End With End With

Worksheets(1).ChartObjects(i).Copy spacer = 20

With m_oPptSlide.Shapes.Paste .top = sngChartStart + spacer .height = m_oPptSlide.Master.height .left = m_oPptSlide.Master.width / 2 End With

Next i End Sub

0 0

Post a comment