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, clock 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…

Topics covered in this Article

Click on the below links to directly jump to that section…

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.
Reference

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 variable of Power Presentation type


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

OR


        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
        TargetPPT.Windows(1).Activate
		'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
        TargetPPT.Save
        'Now close both the PPTs
        SourcePPT.Close
        TargetPPT.Close
      
        Set SourcePPT = Nothing
        Set TargetPPT = Nothing
        pPowerPoint.Quit 'close the powerpoint
     
End Function

Few helpful VBA Tricks related to Slides

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

Trick to paste at the end of the presentation

TargetPPT.Slides.Paste

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

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!