PowerPoint Slides From Excel List

Written by Debra Dalgleish from Contextures Blog

If you have a list of names or topics in Excel, here’s how you can quickly create PowerPoint slides from that list. Unlike most of my macros, these run in PowerPoint, to pull the data from Excel. That was easier than writing Excel macros to make things happen in PowerPoint!

Create a Slide for Each Excel Item

In these macros, the main PowerPoint slide is duplicated. Then, the data from Excel is added to the text boxes on the new slide.

There are 4 macros, with variations for

1 or 2 text boxes on the slide
all Excel rows, or based on criteria

You can download the free files from my Contextures site. There’s a zipped folder with the PowerPoint presentation, and an Excel file with data for the slides.

Slide Data in Excel List

There’s a small named table in the sample Excel file, with data for the PowerPoint slides.

You can use this for testing, before trying the macros with your own Excel data.

PowerPoint Main Slide

In PowerPoint, the first slide in the presentation has two text boxes.

The macro duplicates the first slide, and puts the Excel information in those text boxes

In the sample file, don’t move the main slide, if you want the macros to work correctly!

PowerPoint Main Slide

That first slide in the presentation is based on a Master slide.

You can edit its Master slide, to change the background or text box settings.
You can also replace that chart icon with your company logo, or add other features.

How to Run Macro to Create Slides

To create PowerPoint slides, from a list in Excel, follow these steps:

Open the Excel file where your list is stored
Activate the sheet where the data is stored – the list must be formatted as a named Excel table
Open the PowerPoint presentation that contains the macros and main slide
Be sure the main slide, that you want to duplicate, is the first slide in the presentation
At the top of PowerPoint, on the View tab, click Macros
Select one of the Create Slides macros, and click Run

Check the New Slides

After the macro runs, you can check the new slides that were added to the PowerPoint presentation.

In this example, I ran the macro for 2 text boxes, and checked column 3 in the data, for a “Y”
Three of the rows have a “Y”, so 3 slides were added to the presentation.

NOTE: This macro code is further down the page, and all four macros are in the sample PowerPoint file.

Save the PowerPoint Slides

After you run the macro, and create the duplicate slides:

Save the PowerPoint file with a new name.
Then, in that new file, delete the main slide and the two notes slides, or hide them.

NOTE: If you simply export the new slides, you’ll lose the formatting, because they’re based on the Master Slide in the original PowerPoint file.

Create a Presentation Template

Another option is to save the PowerPoint file in PowerPoint Macro-Enabled Template (potm) format.

By default, that file will be saved in your Custom Office Templates folder.

Then, to create a new set of slides from Excel data:

In PowerPoint, click File, then click New
In the Templates section, click Personal
Click on the Slides from Excel template, then click Create

PowerPoint Slides From Excel Macro Code

Here is the code for the CreateSlidesTest_Text2 macro that fills two text boxes on a slide, and checks the Excel table for criteria.

NOTE: To use the macro with a different Excel table, you can change the variable settings, in this section of the macro code.

     'columns with text for slides
     col01 = 1
     col02 = 2
     'test column and criterion
     colTest = 3
     strTest = "y"

This macro creates slides for items in the Excel list, after checking a criteria cell, and fills 2 text boxes.

In Excel, checks the test column (colTest), and creates a slide if it contains the specified text string (strTest)
In the PowerPoint slide, text from the specified columns (col01 and col02), is entered in the 1st text box and 2nd text box

Sub CreateSlidesTest_Text2()
'https://www.contextures.com
'create slide for names
' that pass criteria test
'fill two text boxes
Dim myPT As Presentation
Dim myMain As Slide
Dim myDup As Slide

Dim xlApp As Object
Dim wbA As Object
Dim wsA As Object
Dim myList As Object
Dim myRng As Object
Dim i As Long
Dim col01 As Long
Dim col02 As Long
Dim colTest As Long
Dim strTest As String

'columns with text for slides
col01 = 1
col02 = 2
'test column and criterion
colTest = 3
strTest = "y"

On Error Resume Next
Set myPT = ActivePresentation
Set myMain = myPT.Slides(1)

Set xlApp = GetObject(, "Excel.Application")
Set wbA = xlApp.ActiveWorkbook
Set wsA = wbA.ActiveSheet
Set myList = wsA.ListObjects(1)
On Error GoTo errHandler

If Not myList Is Nothing Then
  
  Set myRng = myList.DataBodyRange
  
  For i = 1 To myRng.Rows.Count
    'Copy first slide, paste after last slide
    If UCase(wsA.Cells(i, colTest).Value) _
        = UCase(strTest) Then
      With myPT
        'Duplicate slide 1, move after last slide
        myMain.Duplicate
        Set myDup = .Slides(2)
        myDup.MoveTo myPT.Slides.Count
        
        'change text in 1st textbox
        myDup.Shapes(1).TextFrame.TextRange.Text _
            = myRng.Cells(i, col01).Value
         
         'change text in 2nd textbox
        myDup.Shapes(2).TextFrame.TextRange.Text _
            = myRng.Cells(i, col02).Value
      End With
    End If
  Next
Else
  MsgBox "No Excel table found on active sheet"
  GoTo exitHandler
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not complete slides"
  Resume exitHandler:
End Sub

Learn More About PowerPoint Macros

There are PowerPoint code samples on the Microsoft site, that helped me get started with these macros to create slides from Excel data.

For more examples, you can check the StackOverflow forum, or PowerPoint sites.

Get the PowerPoint Slide Files

You can download the free files from my Contextures site. There’s a zipped folder with the PowerPoint presentation, and an Excel file with data for the slides.

The macros that create slides are stored in the PowerPoint file. There are no macros in the Excel workbook

______________________________