This is a continuation of my previous article about Interaction with Power point Slides using Excel Macro. In my previous article we learn how to create a New Power Point Presentation using Excel Macro (by creating Power Point Object).

Bit of a Story about this Article:

This article I planned to write due an incident took place at my work. Friday evening, clocking is ticking to 5 PM..All set to go for the weekend… and then there was a request from my BOSS to do some copy paste work in bunch of Sales PPTs. There were around 20-30 PPTs, each of them having at least 5-6 Slides in it.
My Job was to consolidate all the slides in one new Power Point Presentation.
In a chat with my Boss, I realized that this activity is more frequently done by him.

Then I put some effort and wrote an Excel Macro to do his Job faster without any manual error. At the end of this article you will find a downloadable, it’s the same version which I created.

Later after building this little tool for him, I realized that I can use this code to make quick presentations out of any dynamically generated reports/data in excel and believe me it was very useful.

However this was a bit of story.. let’s get back to the topic…

In this article you will learn followings:

1. How to interact with power point by adding Power Point Application reference in Excel macro.
2. How to read all the slides from a Power Point Presentation one by one using Excel Macro
3. How to copy and Paste a slide from one Power Point to Another

What is the Power Point Application Object reference and How to add it

The reference which you need to add in your Excel Code is Microsoft Power Point (12, 13, 14,15) Object Library as shown in the below picture.

1. Open your VBE Code Screen (by Pressing ALT+F11)
2. From VBE screen go to Tools –> References
3. On Clicking on “References” you will see following screen, where you can look for the relevant reference and add it by selecting it.

Now since, reference is already added, you don’t need to create an Object for Power Point using CreateObject keywords like it is done in my previous article.

Now we can define a new varible of Power Presentation type

        Dim newPowerPoint As PowerPoint.Application
        Set newPowerPoint = New PowerPoint.Application


        Dim newPowerPoint As New PowerPoint.Application

How to traverse all the slides of a Power Point Presentation

Function get_Slide_Count()
    Dim pPath As String
    Dim pCount As Integer
    Dim pPowerPoint As New PowerPoint.Application
    Dim pPresentation As PowerPoint.Presentation
    'Full Path of your Power point presentation
    pPath = "......\PPT-Tutorial.pptx" 'For example
    Set pPresentation = pPowerPoint.Presentations.Open(pPath)
    'Get the total count of slides
    pCount = pPresentation.Slides.Count
    'Display message with total count
    MsgBox "Total No# of Slides is: " & pCount

    pPresentation.Close 'Close the power point presentation
    pPowerPoint.Quit 'now quit the power point Application
    'Set both the Object variables to Nothing
    Set pPresentation = Nothing
    Set pPowerPoint = Nothing
End Function

3. How to copy and Paste a slide from one Power Point to Another

In the below example, you also learn how to choose a particular slide from a bunch of slides in a single presentation.

Function CopyASlideFromOneToAnotherPPT()

        Dim pPowerPoint As PowerPoint.Application
        Set pPowerPoint = New PowerPoint.Application ' New powerpoint created
        Dim SourcePPT As PowerPoint.Presentation
        Dim TargetPPT As PowerPoint.Presentation
        pPowerPoint.Visible = msoTrue
        'Full Path of your Power point presentation
        SourcePPTPath = VBA.Environ("userprofile") & "\desktop\Parth-Presentation-sample.pptx" 'For example
        TargetPPTPath = VBA.Environ("userprofile") & "\desktop\Parth-Presentation-sample2.pptx" 'For example
        Set SourcePPT = pPowerPoint.Presentations.Open(SourcePPTPath)
        Set TargetPPT = pPowerPoint.Presentations.Open(TargetPPTPath)
        'Copy Slide#3 and paste it in Target Presentation as a Slide#4
        SourcePPT.Slides(3).Copy 'copy the 3rd slide of the presentation
		'it will paste as a 4th slide of the target presentation
		'If you do not pass any parameter then it always pastes the slide at the end
        TargetPPT.Slides.Paste (4) 
        'save Target PPT
        'Now close both the PPTs
        Set SourcePPT = Nothing
        Set TargetPPT = Nothing
        pPowerPoint.Quit 'close the powerpoint
End Function

From the above example, here are few more little variations which may help you:

Trick to paste at the end of the presentation


Trick to paste as a Second Last Slide of your presentation

TargetPPT.Slides.Paste (TargetPPT.Slides.Count - 1) 

After a long read, here is your link to download it and Enjoy playing around the code !!! DO NOT forget to provide your feedback about anything you learn here in this forum 🙂

Download Now