Excel VBA to Powerpoint: Copying a cell to textbox in an existing slide

Question

I'm a beginning to excel vba exporting and control information to other programs.

The task: Nameplates need to be printed and distributed to personnel to label their equipment. We have a huge equipment masterlist, the necessary information (machine ID) is only in one column of the excel sheet. The template for the sticker is in powerpoint.

The steps are:

PowerPoint should open the template
Excel should give one cell of information into one slide.
A duplicate slide should be made of the template slide
Step number 2 should be repeated except now the program has moved onto a different slide and different cell to copy and paste.

Here is my code so far:

    Sub Sammple()
   'Declaring Variables
    Dim TemplateName As String
    Dim pptpres As Object
    Dim mySlide As Object
    Dim myPresentation As Presentation
    Dim TextBox As Object
    Dim i As Integer
    Dim j As Integer

    Set pptpres = CreateObject("Powerpoint.Application")
       With pptpres
            .Visible = True
           .Presentations.Open ("FileNameHere")
            For i = 1 To 400
                .ActivePresentation.Slides(1).Duplicate.Item (1)
                Next
            For j = 3 To 514
                Cells(j, 4).Copy
                Next
        End With


End Sub

As you can tell, I've gotten the code down for excel to make 400 duplicate slides. I can't quite understand how to make excel copy text from the cell to a textbox into powerpoint then do it over and over until it reaches the end of the column range.

Any help pointing in the right direction would be great. You'd be saving me over 1000 copy and paste clicks.

Hi Peter, thanks for commenting. That helps me a little in terms of knowing how to declare something (powerpoint.shape vs shape) but it doesn't help me in determining how to tell vba to write into a textbox then duplicate into a blank cell. — Jorge Guevara
You're making 400 copies of the slide but you have 514 cells to copy over? — Tim Williams

Justanewbie Justanewbie · Accepted Answer · 2018-06-28T05:10:24

Do you mean the following?

When you have duplicated a PP slide with already a textbox defined in it, that you want to fill in the value of an excel cell into the textbox of your PP slide?

That is if the textbox in every slide has the same name, you can check it by going to Powerpoint, select your object (textbox), go to Format, Selection Pane, and there you can see the name of your textbox. In this case it is "Title 1"

Dim pp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.slide
Set pp = CreateObject("PowerPoint.Application")

Set PPPres = pp.Presentations.Open ("Filenamehere")
Set PPSlide = PPPres.Slides.Add(Slidestart, ppLayoutTitleOnly)

Sheets("SHEET NAME OF EXCEL").Activate
PPSlide.Shapes("Title 1").TextFrame.TextRange.Text = cells(i,j).text

Excel VBA to Powerpoint: Copying a cell to textbox in an existing slide

1 Answers