ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

 

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

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!

 

 
Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2024    Ole P. Erlandsen   All rights reserved
E-mail Contact Address