Macro 99: Sending Excel Data to a PowerPoint Presentation
It’s been said that up to 50 percent of PowerPoint presentations contain data that has been copied
straight out of Excel. This is not difficult to believe. It’s often much easier to analyze and create charts
and data views in Excel than in PowerPoint. After you’ve created those charts and data views, why
wouldn’t you simply move them into PowerPoint? The macro in this section allows you to dynami-
cally create PowerPoint slides that contain data from a range you specify.
How it works
In this example, you are copying a range from an Excel file and pasting that range to a slide in a
newly created PowerPoint presentation.
Keep in mind that because this code is run from Excel, you need to set a reference to the Microsoft
PowerPoint Object Library. Again, you can set the reference by opening the Visual Basic Editor in
Excel and selecting Tools➜References. Scroll down until you find the entry Microsoft PowerPoint XX
Object Library, where the XX is your version of PowerPoint. Select the check box next to the entry.
Sub CopyRangeToPresentation ()
‘Step 1: Declare your variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideTitle As String
‘Step 2: Open PowerPoint and create new presentation
Set PP = New PowerPoint.Application
Set PPPres = PP.Presentations.Add
PP.Visible = True
‘Step 3: Add new slide as slide 1 and set focus to it
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
PPSlide.Select
‘Step 4: Copy the range as a picture
Sheets(“Slide Data”).Range(“A1:J28”).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
‘Step 5: Paste the picture and adjust its position
PPSlide.Shapes.Paste.Select
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters,
True PP.ActiveWindow.Selection.ShapeRange.Align
msoAlignMiddles, True
‘Step 6: Add the title to the slide
SlideTitle = “My First PowerPoint Slide”
PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle
‘Step 7: Memory Cleanup
PP.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set PP = Nothing
End sub
1. Step 1 declares four variables: PP is an object variable that exposes the PowerPoint
Application object; PPPres is an object variable that exposes the PowerPoint Presentation
object; PPSlide is an object variable that exposes the PowerPoint Slide object; and
SlideTitle is an string variable used to pass the text for the slide title.
2. Step 2 opens PowerPoint with an empty presentation. Note that we are setting the
Visible property of the PowerPoint application to True. This ensures that you can see the
action as the code runs.
3. Step 3 adds a new slide to the presentation using the Add method of Slide object. Note
that we are using the ppLayoutTitleOnly, ensuring the slide is created with a title text
frame. We then take an extra step here and actually set focus on the slide. That is to say, we
explicitly tell PowerPoint to select this slide, making it active.
4. Step 4 uses the CopyPicture method to copy the target range as a picture. The range
being copied here is range A1 to J28 in the Slide Data tab.
5. Step 5 pastes the picture into the active slide and centers the picture both horizontally and
vertically.
6. Step 6 stores the text for the title in a string variable, and then passes that variable to
PowerPoint to apply text to the title text frame.
7. Step 7 releases the objects assigned to our variables, reducing the chance of any problems
caused by rogue objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
