Accessing PowerPoint controls through Excel VBA
Creating a PowerPoint presentation using data from Excel is very common. You can simply create a new PowerPoint, copy all the data, and you’re done. But consider a periodic report — a sales report, for example. Each month you need to create a PPT with data in Excel that has the same structure, and only the figures change. Wouldn’t it be great if you could generate the presentation with the click of a button?
This article walks you through creating a PowerPoint presentation automatically through Excel and populating data from Excel into the presentation. Let us consider this scenario. You are creating a country-wise monthly report. So, in sheet 1, you input the list of countries. For each country, there will be a separate slide, the data for which is present in the following sheets. In this example, we will also see how to create a text box through Excel on each slide and populate data.
First let’s have a look at the data in Excel. In the first sheet we have the list of country names.
And for each country, you have a sheet by the same name. Here you will have the actual data for each slide.
Before we have a look at the code, you need to make sure the “Microsoft PowerPoint Object Library” is included in your VBA editor. For that, go to Tools > Reference and select the library as shown:
If you fail to do so, you will get a compile error.
And now let’s go through the code.
-
-
-
-
-
- We need to start by creating an Instance of PowerPoint and then creating a new presentation.
Set PowerPointApp = CreateObject(class:="PowerPoint.Application") Set myPresentation = PowerPointApp.Presentations.Add
Presentations.Add method creates a presentation and returns a Presentation object that represents the new presentation. It does not contain any slides.
You can get more details on the presentation object here. - Next you get the list of countries, loop through them and get the name of each country
Set nameRng = ThisWorkbook.Sheets("Sheet1").Range("B3:B5") For i = 1 To nameRng.Rows.count countryName = nameRng.Cells(i, 1).Value
- For each country add a slide to the presentation created and assign it to the mySlide object
Set mySlide = myPresentation.Slides.Add(i, 23) '23 = ppLayoutTwoObjectsOverTextHerethe
Slides.Add takes two arguments:
– First argument is the position where the slide is to be inserted. The slide in our example will be inserted at the last position.
– The second argument is the Slide Layout. A complete list of this enumeration can be found here. We have selected “ppLayoutTwoObjectsOverText” as we need two objects for table and graph. And another placeholder at the bottom for the text box we will be inserting (containing the country name). - Next we add title to the slide
Set ttlBox = mySlide.Shapes("Title 1") ttlBox.TextFrame2.TextRange.Characters.Text = countryName + " - Data"
– The slide.Shapes() method returns a Shapes collection that represents all the elements that have been placed or inserted on the specified slide, slide master, or range of slides. As we need to access the Title Text Box, we refer it to by the name “Title 1” (which is the default name for the title text box).
– TextFrame2.TextRange.Characters.Text refers to the text of a shape (Title Text Box in this case)
If you want to add text to text boxes using VBA in PowerPoint refer to this article. - Copy the data table from Excel using
Set rng = ThisWorkbook.Sheets(countryName).Range("B2:F7") rng.Copy
And paste it to PowerPoint using:
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
DataType argument specifies a format for the Clipboard contents when they’re inserted into the document. You can get a complete list here.
- You can also format the shape pasted. For example, to set the position and size of the pasted data use:
myShape.Left = 90 myShape.Top = 200
- Similarly we will copy the charts from Excel and paste it to the next shape in the PowerPoint
Set chart = ThisWorkbook.Sheets(countryName).Shapes(1) chart.Copy mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile Set myShape = mySlide.Shapes(mySlide.Shapes.count) myShape.Left = 550 myShape.Top = 150 myShape.Height = 150
- Next we add a text box to each slide using the AddTextBox method. It takes 5 arguments. First is the orientation of text which is to horizontal. The next four arguments specify the position and size of the text box.
Next we set the text and formatting of the text boxWith mySlide.Shapes.AddTextbox(Orientation:=1, Left:=480, Top:=440, Width:=420, Height:=40).TextFrame.TextRange .Text = nameRng.Cells(i, 1).Value .Font.Bold = msoTrue .Font.name = "Calibri" .Font.Size = 32 .ParagraphFormat.Alignment = ppAlignCenter End With
- And finally we activate the PowerPoint after ending the for loop
PowerPointApp.Visible = True PowerPointApp.Activate
- We need to start by creating an Instance of PowerPoint and then creating a new presentation.
-
-
-
-
In this way you can access all the controls of a PowerPoint Application through Excel VBA. And just with a click of button you can generate the presentation. The final output will look like this.
You can then modify the content and formatting to suit your requirements, using Excel of course.
Here is the code put together.
Sub createPPT() Dim rng As Range Dim PowerPointApp As Object Dim myPresentation As Object Dim mySlide As Object Dim myShape As Object Dim count As Integer Dim countryName As String 'Create an Instance of PowerPoint Set PowerPointApp = CreateObject(class:="PowerPoint.Application") Application.ScreenUpdating = False 'Create a New Presentation Set myPresentation = PowerPointApp.Presentations.Add 'Loop through to create required number of slides Set nameRng = ThisWorkbook.Sheets("Sheet1").Range("B3:B5") For i = 1 To nameRng.Rows.count 'Get the name of the country countryName = nameRng.Cells(i, 1).Value 'Add a slide to the Presentation Set mySlide = myPresentation.Slides.Add(i, 23) '23 = 4 objects 'Add a title to the slide Set ttlBox = mySlide.Shapes("Title 1") ttlBox.TextFrame2.TextRange.Characters.Text = countryName + " - Data" 'Copy Excel Range from each sheet Set rng = ThisWorkbook.Sheets(countryName).Range("B2:F7") rng.Copy 'Paste to new slide in PowerPoint and position mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile Set myShape = mySlide.Shapes(mySlide.Shapes.count) 'Set position: myShape.Left = 90 myShape.Top = 200 ' Copy chart from each sheet Set chart = ThisWorkbook.Sheets(countryName).Shapes(1) chart.Copy 'Paste to PowerPoint mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile Set myShape = mySlide.Shapes(mySlide.Shapes.count) 'Set position: myShape.Left = 550 myShape.Top = 150 myShape.Height = 150 'Add a new text box to each slide 'Each text box will be populated with a name from Sheet1 With mySlide.Shapes.AddTextbox(Orientation:=1, Left:=480, Top:=440, Width:=420, Height:=40).TextFrame.TextRange .Text = nameRng.Cells(i, 1).Value .Font.Bold = msoTrue .Font.name = "Calibri" .Font.Size = 32 .ParagraphFormat.Alignment = ppAlignCenter End With Next i Application.ScreenUpdating = True 'Make PowerPoint Visible and Active PowerPointApp.Visible = True PowerPointApp.Activate 'Clear The Clipboard Application.CutCopyMode = False End Sub