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