Search code examples
excelvbapowerpointexcel-2019

Use VBA to direct cell contents from Excel into PowerPoint presentation

I am using VBA code in Excel to create a slide in PowerPoint for each row of the spreadsheet. The following code works correctly in opening a new presentation and creating a new slide for each row as highlighted in my spreadsheet. It places the 1st cell of the row within the Title placeholder of the new slide:

Sub LoopRowsSelected()

Dim DataRange As Range
Dim DataRow As Range
Dim DataColumn As Range


Dim AppPPT As PowerPoint.Application
Dim Prs As PowerPoint.Presentation
Dim Sld As PowerPoint.Slide

Set AppPPT = New PowerPoint.Application
Set Pres = AppPPT.Presentations.Open("C:\Test\Sample.potx")

AppPPT.Visible = True

Set DataRange = Selection

For Each DataRow In DataRange.Rows
    
    Set Sld = Pres.Slides.AddSlide(Pres.Slides.Count + 1, Pres.SlideMaster.CustomLayouts(1))
 
        Sld.Shapes.Title.TextFrame.TextRange.Text = DataRow.Cells(1, 1)

    Next DataRow

End Sub

I had thought by naming each placeholder within the template differently, and then by copying this line:

Sld.Shapes.Title.TextFrame.TextRange.Text = DataRow.Cells(1, 1)

And adding it to look like this:

Sld.Shapes.Description.TextFrame.TextRange.Text = DataRow.Cells(1, 2)

It would insert the second cell of the row into the placeholder named "Description". It comes back with an error stating "Compile error: Method or data member not found". Obviously, I'm barking up the wrong tree in thinking "Title" is the actual "Title" selection object name in the template.

My question is, what do I need to do differently to direct the cell contents to the correct placeholder in the PPT template? Mind you (as you've already figured out), I'm not a programming, VBA, or Excel expert. I do not have a very good understanding of methods and hierarchy of elements, which I'm sure is causing me issues.

Solution

  • Use the following:

     Sld.Shapes("Description").TextFrame.TextRange.Text = DataRow.Cells(1, 2)

    When you name the placeholders in the Master Slide, the names do not automatically transfer over to a created slide. You'll have to know what those placeholder names are in order to code it correctly in the VBA. If a regular text frame, the first of them will most likely be "TextBox #" where the "#" symbol would be the incremented number as more objects get put into the master slide. You can figure out what the names are by going to the ribbon and selecting Format -> Selection Pane, then selecting the object you want to know the name of in order to highlight it in the selection pane.