Listing Automating Power Point Presentation Creation from Excel

Option Explicit

Sub CreatePresentation()

Dim ppt As PowerPoint.Application

Dim pres As PowerPoint.Presentation

Dim sSaveAs As String

Dim ws As Worksheet

Dim chrt As Chart

Dim nSlide As Integer

On Error GoTo ErrHandler

Set ws = ThisWorkbook.WorksheetsO'Reports")

' Create a new instance of PowerPoint Set ppt = New PowerPoint.Application

' Create a new presentation Set pres = ppt.Presentations.Add pres.ApplyTemplate _

"c:\program files\microsoft office\templates" &

"\presentation designs\maple.pot"

With pres.Slides.Add(1, ppLayoutTitle)

.Shapes(1).TextFrame.TextRange.Text = "October Sales Analysis"

.Shapes(2).TextFrame.TextRange.Text = "11/5/2003" End With

' Copy data

CopyDataRange pres, ws.Range("Sales_Summary"), 2, 2 CopyChart pres, ws.ChartObjects(1).Chart, 3, 1 CopyDataRange pres, ws.Range("Top_Five"), 4, 2

' Save & close the presentation file sSaveAs = GetSaveAsName("Save As") If sSaveAs <> "False" Then pres.SaveAs sSaveAs End If pres.Close

ExitPoint:

Application.CutCopyMode = False Set chrt = Nothing Set ws = Nothing Set pres = Nothing Set ppt = Nothing Exit Sub ErrHandler:

MsgBox "Sorry the following error has occurred: " & _ vbCrLf & vbCrLf & Err.Description, vbOKOnly Resume ExitPoint End Sub

Private Sub CopyDataRange(pres As PowerPoint.Presentation, _ rg As Range, nSlide As Integer, _ dScaleFactor As Double)

' copy range to clipboard rg.Copy

' add new blank slide pres.Slides.Add nSlide, ppLayoutBlank

' paste the range to the slide pres.Slides(nSlide).Shapes.PasteSpecial ppPasteOLEObject ' scale the pasted object in PowerPoint pres.Slides(nSlide).Shapes(1).ScaleHeight dScaleFactor, msoTrue pres.Slides(nSlide).Shapes(1).ScaleWidth dScaleFactor, msoTrue

' Center Horizontally & Vertically

' Might be a good idea to move this outside this procedure ' so you have more control over whether this happens or not CenterVertically pres.Slides(nSlide), _

pres.S1ides(nS1ide).Shapes(1) CenterHorizontally pres.Slides(nSlide), _ pres.S1ides(nS1ide).Shapes(1)

End Sub

Private Sub CopyChart(pres As PowerPoint.Presentation, _ chrt As Chart, nSlide As Integer, _ dScaleFactor As Double)

' copy chart to clipboard as a picture chrt.CopyPicture xlScreen

' add slide pres.Slides.Add nSlide, ppLayoutBlank ' copy chart to PowerPoint pres.S1ides(nS1ide).Shapes.PasteSpecia1 ppPasteDefault ' scale picture pres.S1ides(nS1ide).Shapes(1).Sca1eHeight dScaleFactor, msoTrue pres.S1ides(nS1ide).Shapes(1).Sca1eWidth dScaleFactor, msoTrue

' Center Horizontally & Vertically

' Might be a good idea to move this outside this procedure ' so you have more control over whether this happens or not CenterVertically pres.Slides(nSlide), _

pres.S1ides(nS1ide).Shapes(1) CenterHorizontally pres.Slides(nSlide), _ pres.S1ides(nS1ide).Shapes(1)

End Sub

Private Function GetSaveAsName(sTit1e As String) As String

Dim sFilter As String sFilter = "Presentation (*.ppt), *.ppt"

GetSaveAsName = _

App1ication.GetSaveAsFi1ename(fi1efi1ter:=sFi1ter, _ Tit1e:=sTit1e) End Function

Private Sub CenterVertically(sl As PowerPoint.Slide, _ sh As PowerPoint.Shape)

Dim lHeight As Long lHeight = sl.Parent.PageSetup.SlideHeight sh.Top = (lHeight - sh.Height) / 2 End Sub

Private Sub CenterHorizontally(sl As PowerPoint.Slide, sh As PowerPoint.Shape)

Dim lWidth As Long lWidth = sl.Parent.PageSetup.SlideWidth sh.Left = (lWidth - sh.Width) / 2 End Sub

CreatePresentation is the main procedure of this listing. Right off the bat, you can tell that this procedure is using early binding just by looking at the variable declarations. CreatePresentation uses two variables that represent a PowerPoint object. The first, ppt, represents the PowerPoint.Applica-tion object. Like the Application object from the Excel object model, PowerPoint.Application is at the top of the PowerPoint object model. The second variable that represents a PowerPoint object is named pres and represents a PowerPoint Presentation object. The Presentation object is roughly analogous to the Workbook object in Excel.

After the variables are declared, the next task is to enable some sort of error handling. You'll encounter an increased probability of errors in code that deals with automation. For this example, I just trap the error, display the error message, and then run the clean-up code denoted by the ExitPoint label.

After I set a reference to the Reports worksheet, it's time to create a new instance of PowerPoint and point your ppt variable to it. Once I have PowerPoint up and running, I create a new presentation using the fall inspiring maple template (which is appropriate for October) and add the title slide.

At this point, I can copy in the data from Excel. Because I need to do a number of things when I'm copying from Excel to PowerPoint, it's a good idea to create a procedure that wraps this task up. To do so, I enter the CopyDataRange procedure. Copying from Excel to PowerPoint is a two statement process.

' copy range to clipboard rg.Copy

' paste the range to the slide pres.Slides(nSlide).Shapes.PasteSpecial ppPasteOLEObject

The first step is to copy the range in Excel to the clipboard. The transfer is completed in PowerPoint by using the PasteSpecial method of the Shapes object and specifying the OLE object option. The remainder of this procedure deals with sizing and positioning (via the CenterVertically and Center-Horizontally procedures). In order to make this procedure more generic (and therefore increase the potential for reuse) it would be a good exercise to either remove the positioning statements or add a parameter that allows you to copy the range onto the slide without repositioning.

The CopyChart procedure is very similar to CopyDataRange. The first difference is that this procedure requires a Chart object as a parameter rather than a Range object. The other difference is in the copy/paste operation.

' copy chart to clipboard as a picture chrt.CopyPicture xlScreen

' copy chart to PowerPoint pres.Slides(nSlide).Shapes.PasteSpecial ppPasteDefault

I have used the CopyPicture method of the Chart object, which copies a picture of the chart to the clipboard. Rather than the OLE object option, I pasted the chart using the default option.

0 0

Responses

  • annett
    HOW TO ADD RANGE COPIES AS PICTURE IN DIFFERENT POWER POINT PRESENTATION USING VB CODE?
    6 years ago

Post a comment