Control PowerPoint from Excel

The example macro below demonstrates how you can create a new PowerPoint presentation.

Note! Read and edit the example code before you try to execute it in your own project!

Sub CreateNewPowerPointPresentation()
' to test this code, paste it into an Excel module
' add a reference to the PowerPoint-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptShape As PowerPoint.Shape
Dim i As Integer, strString As String
    Set pptApp = CreateObject("PowerPoint.Application")
    Set pptPres = pptApp.Presentations.Add(msoTrue) ' create a new presentation
    ' or open an existing presentation
    ' Set pptPres = pptApp.Presentations.Open("C:\Foldername\Filename.ppt")
    
    ' apply a slide template
    pptPres.ApplyTemplate "C:\Program Files\Office XP\Templates\Presentation Designs\Globe.pot"
    
    With pptPres.Slides
        Set pptSlide = .Add(.Count + 1, ppLayoutText) ' add a slide
    End With
    With pptSlide ' add contents to a slide
        .Shapes(1).TextFrame.TextRange.Text = "Slide Title" ' add a slide title
        For i = 1 To 5 ' create slide text content
            strString = strString & "Item number #" & i & Chr(13)
        Next i
        strString = Left$(strString, Len(strString) - 1)
        .Shapes(2).TextFrame.TextRange.Text = strString ' add text content
    End With
    
    ThisWorkbook.Worksheets(1).Range("A3:D10").Copy ' copy an Excel range
    
    With pptPres.Slides
        Set pptSlide = .Add(.Count + 1, ppLayoutText) ' add a slide
    End With
    With pptSlide
        .Shapes(1).TextFrame.TextRange.Text = "Slide Title" ' add a slide title
        .Shapes(2).Delete ' remove the text box
        .Shapes.Paste
        With .Shapes(.Shapes.Count)
            .Left = 50
            .Top = 100
            .Width = 600
            .Height = 400
        End With
    End With
    
    With pptPres.Slides
        Set pptSlide = .Add(.Count + 1, ppLayoutText) ' add a slide
    End With
    With pptSlide
        .Shapes(1).TextFrame.TextRange.Text = "Slide Title" ' add a slide title
        .Shapes(2).Delete ' remove the text box
        .Shapes.PasteSpecial ppPasteBitmap
        With .Shapes(.Shapes.Count)
            .Left = 50
            .Top = 150
            .Width = 600
            '.Height = 250
        End With
    End With
    
    With pptPres.Slides
        Set pptSlide = .Add(.Count + 1, ppLayoutText) ' add a slide
    End With
    With pptSlide
        .Shapes(1).TextFrame.TextRange.Text = "Slide Title" ' add a slide title
        .Shapes(2).Delete ' remove the text box
        .Shapes.PasteSpecial ppPasteOLEObject
        With .Shapes(.Shapes.Count)
            .Left = 50
            .Top = 150
            .Width = 600
            '.Height = 250
        End With
    End With
    
    ThisWorkbook.Worksheets(1).ChartObjects(1).Copy ' copy an Excel chart item
    With pptPres.Slides
        Set pptSlide = .Add(.Count + 1, ppLayoutTitleOnly) ' add a slide
    End With
    With pptSlide
        .Shapes(1).TextFrame.TextRange.Text = "Slide Title" ' add a slide title
        .Shapes.PasteSpecial ppPasteDefault
        With .Shapes(.Shapes.Count)
            .Left = 120
            .Top = 125.125
            .Width = 480
            .Height = 289.625
        End With
    End With
    
'    ThisWorkbook.Charts(1).ChartArea.Copy ' copy an Excel chart
'    With pptPres.Slides
'        Set pptSlide = .Add(.Count + 1, ppLayoutTitleOnly) ' add a slide
'    End With
'    With pptSlide
'        .Shapes(1).TextFrame.TextRange.Text = "Slide Title" ' add a slide title
'        .Shapes.PasteSpecial ppPasteDefault
'        With .Shapes(.Shapes.Count)
'            .Left = 120
'            .Top = 125.125
'            .Width = 480
'            .Height = 289.625
'        End With
'    End With
    
    Application.CutCopyMode = False ' end cut/copy from Excel
    Set pptSlide = Nothing
    
    On Error Resume Next ' ignore errors
    Kill "C:\Foldername\MyNewPresentation.ppt"
    With pptPres
        .SaveAs "C:\Foldername\MyNewPresentation.ppt"
        '.Close ' close the presentation
    End With
    On Error GoTo 0 ' resume normal error handling
    Set pptPres = Nothing
    
    pptApp.Visible = True ' display the application
    'pptApp.Quit ' or close the PowerPoint application
    Set pptApp = Nothing
End Sub

Document last updated 2005-07-25 09:43:16 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-01-10 15:25:22 CET):

Re: Add reference?
Please read Basic Information About OLE Automation.

Ankur Jain from India wrote (2006-01-10 05:42:16 CET):

Add reference?
Hi..

How do I add the reference to the Powerpoint Library?

Ole P. from Norway wrote (2005-07-25 09:46:28 CET):

Re: Move the chart from Excel to powerpoint
The example above is updated with code showing how to copy a chart item or chart from Excel into PowerPoint.

Mark Yan from Raleigh, North Carolina wrote (2005-07-24 21:53:05 CET):

Move the chart from Excel to powerpoint
Is there a way to transfer the chart in Excel to powerpoint or Word with VBA code?

Ole P. from Norway wrote (2005-03-09 22:41:07 CET):

Re: After years of searching...
The code below is not tested, but it might give you the necessary information you are looking for:

Dim objChart As Chart, s As Long
Set objChart = ActivePresentation.Slides(1).Shapes(1).OLEFormat.Object
For s = 1 To objChart.SeriesCollection.Count
With objChart.SeriesCollection(s)
' do something...
End With
Next s
objChart.Application.Update ' update the chart
objChart.Application.Quit ' quit the chart application
Set objChart = Nothing

Steven F from London wrote (2005-03-09 19:20:09 CET):

After years of searching...
I'm finally starting to believe that there's no way to control the chart data sheet in PPT 2000, as someone who works in a very graph-orientated business this is absolutely gutting.

If anyone knows different I'd love to know!