Use these free macros to quickly create PowerPoint slides from an Excel list. You can fill 1 or 2 text boxes in each slide, and select which columns to use from the Excel list. Create a slide for every item in the list, or only items with specific text in one column, such as "Y".
Why would you use macros to create PowerPoint slides from an Excel list? It's a quite way to build a slide deck, without copying and pasting between those two applications.
For example, create quick slides to:
This page has PowerPoint macros that create slides from an Excel list. You can copy the macros into your PowerPoint file, and run them there.
Or, download the folder with sample files, at the end of this page. The folder contains:
In the sample Excel file, there is a table with 3 columns - Name (1), Dept (2), Attend (3)
NOTE: You can change the macros so they use information from different columns
In the sample PowerPoint file that you can download below, there are:
In the sample PowerPoint file, there is a main slide with two text boxes. The macros copy that slide, and create individual slides from it, using the text in the Excel list.
NOTE: The macros use Slide #1 when creating duplicates, so be sure your main slide in that #1 position.
In the sample file, the main slide's formatting is based on its Slide Master.
To change the Slide Master:
There are 2 slides with brief notes about the macros. You can leave those in the PowerPoint file, or delete them.
The PowerPoint file is saved in pptm (macro-enabled) format, and contains four macros. The macro code is further down this page.
The macros that create slides are stored in the PowerPoint file. There are no macros in the Excel workbook.
To create PowerPoint slides, from a list in Excel, follow these steps:
TIP: After you run the macro, and create the duplicate slides, save the PowerPoint file with a new name. Then, delete the main slide and the two notes slides, or hide them.
Below is the code for the 4 macros in the PowerPoint presentation file.
In each macro, there are one or more variables that you can change, to match the TABLE column numbers where data is stored in your workbook.
NOTE: These will be different from worksheet column numbers, if your Excel table doesn't start in column A
A) These variables set the TABLE column numbers to use for the text boxes:
B) These variables set the TABLE column numbers to use for the criteria column and text:
The macro compares UPPER CASE text for the criteria, so it will match "yes" with "YES" or "Yes", or other variations.
This macro creates slides for items in the Excel list, after checking a criteria cell, and fills 1 text box.
NOTE: Change those variable settings to match your Excel columns
Sub CreateSlidesTest_Text1() 'https://www.contextures.com 'create slide for names ' that pass criteria test 'fill one text box 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 colTest As Long Dim strTest As String 'column with text for slides col01 = 1 '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 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
This macro creates slides for items in the Excel list, after checking a criteria cell, and fills 2 text boxes.
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
This macro creates slides for all items in the Excel list, and fills 1 text box.
Sub CreateSlides_Text1() 'https://www.contextures.com 'create slide for each name in list 'fill one text box 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 'column with text for slides col01 = 1 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 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 End With Next i 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
This macro creates slides for all items in the Excel list, and fills 2 text boxes.
Sub CreateSlides_Text2() 'https://www.contextures.com 'create slide for each name in list '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 'columns with text for slides col01 = 1 col02 = 2 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 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 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
Macros to Sheets as PDF Format
Last updated: January 20, 2021 4:14 PM
Contextures RSS Feed