Sending Excel Data to a PowerPoint Presentation - dummies

Sending Excel Data to a PowerPoint Presentation

By Michael Alexander

It has been said that up to 50 percent of PowerPoint presentations contain data that has been copied straight out of Excel. This statement is not difficult to believe. It’s often much easier to analyze and create charts and data views in Excel than in PowerPoint. After those charts and data views have been created, why wouldn’t you simply move them into PowerPoint? The macro in this section allows you to dynamically create PowerPoint slides that contain data from a range you specify.

You can download a working example of sending Excel data to PowerPoint.

How the macro works

In this example, you copy 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 will be run from Excel, you need to set a reference to Microsoft PowerPoint Object Library. You can set the reference by opening Visual Basic Editor in Excel and choosing Tool→References. Scroll down until you find the entry Microsoft PowerPoint xx Object Library, where the xx is your version of PowerPoint. Place a check in 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)
'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
  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
  Set PPSlide = Nothing
  Set PPPres = Nothing
  Set PP = Nothing
End sub

In Step 1, you declare 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.

Step 2 opens PowerPoint with an empty presentation. Note that you set the Visible property of the PowerPoint application to True to ensure that you can see the action as the code runs.

In Step 3, you add a new slide to the presentation using the Add method of Slide object. Note that you are using ppLayoutTitleOnly, ensuring that the slide is created with a title text frame. You then take an extra step and set focus on the slide. That is to say, you explicitly tell PowerPoint to select this slide, making it active.

In Step 4, you use 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.

Step 5 pastes the picture into the active slide and centers the picture both horizontally and vertically.

In Step 6, you store the text for the title in a string variable, and then pass that variable to PowerPoint to apply text to the title text frame.

In Step 7, you release the objects assigned to your variables, reducing the chance of any problems caused by rouge objects that may remain open in memory.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

  1. Activate Visual Basic Editor by pressing Alt+F11.

  2. In the Project window, find your project/workbook name.

  3. Choose Insert→Module.

  4. Type or paste the code.