Automating PowerPoint from Excel

Automating PowerPoint from Excel

You can also connect to PowerPoint though other applications (like Excel and Word). As as first step, you must refer to an instance of PowerPoint.

There are two ways of doing it – early binding and late binding .

Open PowerPoint – Early Binding

In 'Early Binding' you must explicitly set a reference to 'Microsoft PowerPoint 16 Object Library' (for MS Office 2019) in the VBE (Visual Basic Editor) using the option Tools->References.

' Early Binding
Dim pptApp As Application
Set pptApp = New PowerPoint.Application

Open PowerPoint – Late Binding

In 'Late Binding' application variable is declared as an object and VBA engine connects to the correct application at run time.

' Late Binding
Dim pptApp As Object
Set pptApp = CreateObject("PowerPoint.Application")

Make Application Visible

After setting the reference to PowperPoint application, you may need to make it visible.

pptApp.Visible = True

Maniplulate PowerPoint

You can use all the methods to manipulate presentations, from within PowerPoint, described above from Excel by just adding the reference to PowerPoint created by you above.

For example

Presentations.Open ("My Presentation.pptx")

has to be used liked this

pptApp .Presentations.Open ("My Presentation.pptx")

Close the Application

Once you have completed what you wanted to do with the PowerPoint application you must close it and should release the reference.

pptApp.Quit
Set pptApp = Nothing

Copy From Excel to PowerPoint

This code will copy a range from Excel to PowerPoint:

Note: It has been kept as simple as possible to show how a range from Excel can be copied to PowerPoint using VBA.

Sub copyRangeToPresentation()

' Open New PowerPoint Instance
Set pptApp = CreateObject("PowerPoint.Application")

With pptApp
    ' Create A New Presentation
    Set ppt = .Presentations.Add
    ' Add A Blank Slide
    Set newSlide = ppt.Slides.Add(1, 12) ' ppLayoutBlank = 12
    ' Copy Range from Active Sheet in Excel
    ActiveSheet.Range("A1:E10").Copy
    ' Paste to Powerpoint as an Image
    newSlide.Shapes.PasteSpecial DataType:=2  '2 = ppPasteEnhancedMetafile
    ' Switch to PowerPoint
    .Activate
End With

End Sub

PowerPoint VBA FAQs

What are macros in PPT?

A Macro is a general term that refers to a set of programming instructions that automates tasks. PowerPoint (PPT) Macros automate tasks in PowerPoint using the VBA programming language.

How do I use VBA in PowerPoint?

To use VBA in PowerPoint, open the VBA Editor (ALT + F11 or Developer > Visual Basic).

How do I create a Macro in PowerPoint?

1. Open the VBA Editor (ALT + F11 or Developer > Visual Basic)
2. Go to Insert > Module to create a Code Module
3. Type 'Sub HelloWorld' and press Enter
4. In between the lines 'Sub HelloWorld' and 'End Sub', type 'MsgBox "Hello World!'
5. You've created a Macro!
6. Now press 'F5' to run the Macro