Macro Needed: Excel Spreadsheet Cells to Powerpoint Text Fields (shifting to new slide with each row of data)


I have an Excel file where each row lists data for an individual person.  I need to transfer the data for each individual in a Powerpoint presentation where each individual has their own slide.  The slide layout should be picture with caption.  The name of the individual should be the title of the slide, the data in the row of that individual less the name column should be the data in the caption text box.  From a separate folder, the picture associated with each individual should be pulled into the picture field in powerpoint (the folder contains individual .jpg files).  I can complete this portion if someone can write the macro line to pull a specific file which matches the name in the Excel doc.




I'll cover the first part here, getting the data into PowerPoint.

Here is some VBA code to get you started:

Sub Data_to_PowerPoint()

    Dim newPowerPoint As PowerPoint.Application
    Dim activeSlide As PowerPoint.Slide
    Dim ExcelRow As Range
    Dim CellRange As Range
    Dim SlideText As Variant

    'The first range of cells in the table.
    Set CellRange = Range("A1:A2")

    'Look for existing powerpoint instance
    On Error Resume Next
    Set newPowerPoint = GetObject(, "PowerPoint.Application")
    On Error GoTo 0

    'Create a PowerPoint
    If newPowerPoint Is Nothing Then

        Set newPowerPoint = New PowerPoint.Application

    End If

    'Setup the presentation in PowerPoint
    If newPowerPoint.Presentations.Count = 0 Then


    End If

    'Make PowerPoint visible
    newPowerPoint.Visible = True

    'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
    For Each ExcelRow In CellRange

        'Add a new slide
        newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
        newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
        Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

        'Create the body text for the slide
        SlideText = Cells(ExcelRow.Row, 1) & Chr(13) & Cells(ExcelRow.Row, 2)

        'Input the title of the slide
        activeSlide.Shapes(1).TextFrame.TextRange.Text = ExcelRow.Value

        'Input the body text for the slide
        activeSlide.Shapes(2).TextFrame.TextRange.Text = SlideText


    AppActivate ("Microsoft PowerPoint")
    Set activeSlide = Nothing
    Set newPowerPoint = Nothing

End Sub

Make sure to enable the PowerPoint library in VBA:

  1. In the VBA window, go to ToolsReferences...
  2. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay.
    • X.0 stands for the version number, it could be, for instance, 14.0 or another number.

What to change:

You need to change some parts of the macro to suit your needs.

  1. Set CellRange = Range("A1:A2")
    • Change Range("A1:A2") to the full length of the first column in your data set.
  2. SlideText = Cells(ExcelRow.Row, 1) & Chr(13) & Cells(ExcelRow.Row, 2)
    • Chr(13) is the bullet point.
    • Cells(ExcelRow.Row, 1) is the cell from which you want to get the data to put in for a bullet point. The 1 here means to get data from the first column in the current row; 2 means from the second column in the current row, etc.
    • Follow the above pattern adding as many Char(13) and cell references for data as you need in order to get all of the desired information into the slide.
    • Each piece needs to be separated using an ampersand & as in the example.

Get this working how you want for your data and then ask another question specific to the photos and we can see about that.


Answer the Question

You must create an account to use the forum. Create an Account or Login