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 box

              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
            
            

          • And finally we activate the PowerPoint after ending the for loop

            PowerPointApp.Visible = True
            PowerPointApp.Activate
            

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