Sending Excel Data to a PowerPoint Presentation
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) 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
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:
Activate Visual Basic Editor by pressing Alt+F11.
In the Project window, find your project/workbook name.
Type or paste the code.