Select PowerPoint Slide using Names in Excel environment

0
votes

I've set up a macro that copy/pastes graphs and tables from a spreadsheet to specific slides in a pre-existing PowerPoint presentation using excel VBA. In the code, I have to specify the exact slide number to copy the excel graph to (done 100+ times).

The issue is that when slides are added/deleted from the presentation in the future, the slide numbers will change and I will have to go through the code and manually update each slide reference.

I'm looking for a way to name slides in PowerPoint then use those names in my excel vba code? Eliminating the risk of slides being added/deleted later on (which will definitely happen).

This post is similar in concept( vba powerpoint select a slide by name), but I can't seem to get it working using the excel vba.

An excerpt of the code I'm using to paste a graph is below. Instead of referencing slide 54, I'd rather use a named slide like "Performance":

Set oPPT = GetObject(, "PowerPoint.Application")
Set presUpdate = oPPT.Presentations("Presentation Name")

With presUpdate.Slides(54)
        .Shapes.PasteSpecial(DataType:=xlBitmap).Select
        .Shapes("Picture 1").Left = 34.01559
        .Shapes("Picture 1").Top = 96.36512
End With

Thanks

1
excelvba

1 Answers

0
votes

Suppose you have 3 topics, Performance, Issues and Sales

In the master layout of the PowerPoint document create 4 slide layouts and rename them to

  1. Performance

  2. Issues

  3. Sales

  4. Others

Once that is done, sort and paste all the content according to the topics (yes this is a painful process, but its one time), by right clicking on the slide and choosing one of the 4 layouts.

If the document contains 100 slides, the below is the scenario

  1. Performance: 20 slides

  2. Issues: 30 slides

  3. Sales: 10 slides

  4. Others: 40 slides

Even if they are reordered you can write a macro that can search for the slide name and relevant changes can be done.

The main point here is that the SLIDE NAME is your INDEX and having them sorted like this will help you in the long run.

Hope this answers your question