Programmatically create [Mail Merge] PowerPoint slides from Excel data

Question

Using Powerpoint 2010 for Mac.

I have a 100-slide presentation that needs to be created weekly. Each slide has an identical template. The elements on each slide consist of 5 text fields and a picture, each slide corresponding to columns of data in an Excel table.

Can anyone point me to a code example or detailed walkthrough of how I can loop through the rows of this Excel file and programmatically create these slides?

Answer 1

This is definately possible, and quite fun :-)

My first recommendation, is to create a powerpoint template using master views, with placeholders and titles all ready to go. Then run the following powerpoint macro, so that you can get the name and index/id for every shape on the page.

Sub nameshapes()

Dim sld As Slide
Dim shp As Shape
Set sld = Application.ActivePresentation.Slides(1)

For Each shp In sld.Shapes
    shp.TextEffect.Text = shp.name & " " & shp.ID
Next
End Sub

This is a quick and dirty piece of code that puts the index & name of each item on your template page, into the shape itself. Memorise, record these. These are your reference points for where you want stuff to go. I'm not going to go over the basics of looping etc, but go over the key code peices.

1) Open and gain control of powerpoint from excel.

Set ppt = CreateObject("PowerPoint.Application")
ppt.Visible = True
Set myPPT = ppt.Presentations.add
myPPT.ApplyTemplate ("Your template here.potx")

I then add all the pages I'll need, (this can vary depending on your application, and the number of rows, and whether you do this at the start, or in the process, will depend on whether you have mapped which page you should put the data onto, in your data)

For x = 1 To NumberOfPages
    myPPT.Slides.AddSlide x, myPPT.SlideMaster.CustomLayouts(2) '2 is the index of the template I wish to use (in master views, the index is the order they're in (starting from 1)
Next

I'm assuming that you know which page you want to update at each row, so:

For Each dr In .rows  'I'm storing my data in a special collection, you will need to adapt this
    Set currSlide = myPPT.Slides(dr.cell("OutputPage").Value) 'go to the right page
    Sheets(dr.cell("SheetName").toString).Activate 'make sure the data you want is active
    ActiveSheet.Range(Names(dr.cell("ChartID").Value)).CopyPicture 'copy the table as a picture, this is easiest for formatting in powerpoint, but you can do lots of things here
    currSlide.Select
    currSlide.Shapes("Content Placeholder " & dr.cell("Output Position").toString).Select 'the output position is the index from the first bit of code,
    ppt.ActiveWindow.View.Paste
next

Now, your application will definitely vary from this but I hope all of the basic necessities are there, and this should be a good starting point.