PowerPoint in Excel VBA
The beauty of VBA is that we can reference other Microsoft products like “Microsoft Word” and “Microsoft PowerPoint”. We usually create reports in excel and then create PowerPoint presentations. All the excel users usually spend a considerable amount of time to prepare the presentation from excel data and reports. If you are spending a considerable amount of time to prepare powerpoint presentations this article will show you how to create power point presentation from excel itself using VBA Coding.
How to Create a PowerPoint Presentation from Excel VBA?
We can create power point presentation in two way one us using “Early Binding” and another one is using “Late Binding”.
In this article, we will show you how to create a power point presentation by using “Early Binding” technique in VBA.
To do this first we need to reference the PowerPoint object model in visual basic editor.
Step 1: Open VBA Editor and then, Go to Tools and References.
Step 2: Now you will see all the references to the VBA Project. Scroll down and Select “Microsoft PowerPoint 15.0 Object Library”.
Step 3: Click on Ok. Now we can access PowerPoint from excel.
Usually, from excel we prepare presentations based on charts and interpretation of the charts. So for this purpose, I have created some simple charts and interpretation in the same worksheet.
Step 4: Start the VBA subprocedure. Now to access VBA PowerPoint we have already enabled PowerPoint object model in the earlier steps, now to access this we need to declare the variable as PowerPoint.Application.
Sub PPT_Example() Dim PPApp As PowerPoint.Application End Sub
Step 5: Not to add the presentation to the VBA PowerPoint we need to declare a variable as PowerPoint.Presentation.
Dim PPPresentation As PowerPoint.Presentation
Step 6: After adding the presentation to the PowerPoint we need to add Slide. To declare the variable as PowerPoint.Slide
Dim PPSlide As PowerPoint.Slide
Step 7: Once the slide is added to the PowerPoint we need to make use of shapes in the PowerPoint i.e. text boxes. To declare a variable as PowerPoint.Shape
Dim PPShape As PowerPoint.Shape
Step 8: Now to access all the charts in the worksheet we need to declare the variable as Excel.ChartObjects.
Dim PPCharts As Excel.ChartObject
Ok, to start off the proceedings these variables are enough.
Step 9: Now we need to launch the PowerPoint from excel. Since PowerPoint is an external object we need to set this as a new power point.
Set PPApp = New PowerPoint.Application
This will launch the new PowerPoint from excel.
Step 10: Now the variable PPApp is equal to the PowerPoint we have launched. Now make this PowerPoint visible and maximize the window.
PPApp.Visible = msoCTrue PPApp.WindowState = ppWindowMaximized
At this moment just run the code using F5 key or manually, you should see PowerPoint app launched like the below one.
Step 11: Now we need to add a presentation to the PowerPoint app we have launched.
Set PPPresentation = PPApp.Presentations.Add
Now we should see the PowerPoint presentation like this.
Step 12: After adding the presentation we need to add a slide.
Set PPSlide = PPPresentation.Slides.Add(1, ppLayoutTitleOnly)
Now this will add the title slide PowerPoint presentation like the below.
Step 13: Now we have more than one chart in the worksheet we need to loop through each chart and paste in the presentation. Below is the code to copy and paste the chart as well as interpretation.
Below is the complete code for you.
Sub PPT_Example() Dim PPApp As PowerPoint.Application Dim PPPresentation As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide Dim PPShape As PowerPoint.Shape Dim PPCharts As Excel.ChartObject Set PPApp = New PowerPoint.Application PPApp.Visible = msoCTrue PPApp.WindowState = ppWindowMaximized 'Add Presentation Set PPPresentation = PPApp.Presentations.Add 'Loop through each chart in the Excel and paste into the PowerPoint For Each PPCharts In ActiveSheet.ChartObjects PPApp.ActivePresentation.Slides.Add PPApp.ActivePresentation.Slides.Count + 1, ppLayoutText PPApp.ActiveWindow.View.GotoSlide PPApp.ActivePresentation.Slides.Count Set PPSlide = PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count) 'Copy the chart and paste in Powerpoint PPCharts.Select ActiveChart.ChartArea.Copy PPSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select 'Add heading to the slide PPSlide.Shapes(1).TextFrame.TextRange.Text = PPCharts.Chart.ChartTitle.Text 'Allignment of the chart PPApp.ActiveWindow.Selection.ShapeRange.Left = 15 PPApp.ActiveWindow.Selection.ShapeRange.Top = 125 PPSlide.Shapes(2).Width = 200 PPSlide.Shapes(2).Left = 505 'Add interpretation If InStr(PPSlide.Shapes(1).TextFrame.TextRange.Text, "Region") Then PPSlide.Shapes(2).TextFrame.TextRange.Text = Range("K2").Value & vbNewLine PPSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("K3").Value & vbNewLine) 'Else if the chart is the "Renewable" consumption chart, then enter the appropriate comments ElseIf InStr(PPSlide.Shapes(1).TextFrame.TextRange.Text, "Month") Then PPSlide.Shapes(2).TextFrame.TextRange.Text = Range("K20").Value & vbNewLine PPSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("K21").Value & vbNewLine) PPSlide.Shapes(2).TextFrame.TextRange.InsertAfter (Range("K22").Value & vbNewLine) End If 'Now let's change the font size of the callouts box PPSlide.Shapes(2).TextFrame.TextRange.Font.Size = 16 Next PPCharts End Sub
You can download this Excel VBA PowerPoint template here – VBA PowerPoint Template
This has been a guide to VBA PowerPoint. Here we learn how to create a PowerPoint presentation by using “Early Binding” technique in VBA Code along with examples and a downloadable template. Below are some useful excel articles related to VBA –
- VBA Selection
- VBA ByRef Function Argument
- Delete Files using VBA
- VBA Charts
- VBA Val Function
- VBA OverFlow Error 6
- VBA ISNULL()
- Create VBA Pivot Table
- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion
- Basic Excel Training
- Advanced Excel Training
- Basic & Advanced VBA Course
- Excel Dashboard Course
- Data Analysis in Excel
- Create VBA Applications