Copying Multiple Excel Charts To PowerPoint Using VBA

In this module, we will go over how to take multiple Excel charts in our workbook and copy it over to a PowerPoint presentation that we will create. This module will be broken into two parts. First, we will assume that all the charts on a single sheet and then copy those charts over to a presentation. After that, we will assume we have multiple charts across multiple worksheets.

Link To YouTube Video:

I did make a YouTube video that will cover this topic, while it is not identical to this exact code the concept is the same. If you would like to watch the video and follow along with the code, feel free to click the link.

Step One: Enable The PowerPoint Object Library

To enable the PowerPoint Object library in Excel VBA, go inside the VBA editor go-to tools, a drop-down will appear at which point you can go-to references. A new window will pop up where you can select different reference libraries. You'll be looking for the Microsoft PowerPoint Object Library. Keep in mind, you might see a different number with your PowerPoint Object Library. For example, mine is Microsoft PowerPoint 16.0 Object Library, this is because I’m on the latest version of Office. This means the number is referring to the version of Office that you have! However, if you’re on an earlier version you will see a different number. Whatever the number is simply enable the library.

Also, because I’ve used the Object Library before it will be at the top of my object explorer. If this is the first time you’ve used this library you will need to scroll down and find it. Good for us though, all the items are in alphabetical order.

STEP TWO: DECLARE YOUR VARIABLES

The first thing we need to do is to declare our variables. They will be broken into two categories, the PowerPoint variables, and the Excel Variables. We need one variable to house our PowerPoint Application, we need a presentation in our application so we need to create a presentation variable, and finally, we will want a slide in our presentation so we will create a slide object variable. Now I’m also going to create one more variable that is related to PowerPoint, and I’ll call this variable a slide index. This will come in handy when I need to loop through my charts and create a slide for each of the charts, but I’ll explain this later.

Finally, we need to declare our Excel variables, in this case, we only have one that we have to declare which is a Chart Object Variable. That variable will house our Chart that we want to export. Here is the first section of the code where we declare our variables.

'Declare PowerPoint Variables
 Dim PPTApp As PowerPoint.Application
 Dim PPTPres As PowerPoint.Presentation
 Dim PPTSlide As PowerPoint.Slide
 Dim SldIndex As Integer
        
'Declare Excel Variables
 Dim Chrt As ChartObject

Step Three: Create a New Instance of PowerPoint

The first thing we need to do once we declare our variables is to create our an instance of the PowerPoint application because it is assumed in this script that PowerPoint is not currently active. To create a new instance of PowerPoint we will set our PPTApp variable equal to a new instance of the PowerPoint application. After we created a new instance of PowerPoint our PPTApp variable will now house this instance. Here is how the code will look:

'Create new PowerPoint Application & make it visible.
 Set PPTApp = New PowerPoint.Application
     PPTApp.Visible = True

Step Four: Create a New Presentation

Now by default when we create a new PowerPoint Application there will be no Presentation within it. This is something we have to do in VBA. To create a new presentation, we have to go into the Presentations Collection that belongs to our PPTApp and then call the Add method of our Presentation collection. Here is the code that creates a new presentation.

'Create a new presentation in the PowerPoint Application
 Set PPTPres = PPTApp.Presentations.Add

Step Five: Declare Our Slide Index Handler

Now in this example, we want to copy multiple charts over to PowerPoint and it is assumed that each of these charts should exist on a separate slide. In order to achieve this, I need to create a variable that will dynamically change so that way when I’m on chart two it will have a value of two, when I’m on chart three it will have a value of three and so on. What I am going to do is create this variable and place a value of one in it and later in the code, I will change this value as I loop through the charts. At this point however it’s okay if it only has a value of one. Here is how I declare my slide index handler.

'Create an index handler for slide creation.
 SldIndex = 1

Step Six: Loop Through Each of the Charts on The Active Sheet

Now our goal is to take each of the charts on our ACTIVE SHEET and export them to PowerPoint, so we need a way to go through each chart on our sheet. Now for us, we have a collection we can leverage that houses all the charts on a particular worksheet. This collection is called the ChartObjects collection and if we take this collection and put it in a For Each Loop we now have a way to copy each chart. Here is how we will build our loop:

'Loop through all the CHARTOBJECTS in the ACTIVESHEET.
 For Each Chrt In ActiveSheet.ChartObjects
        
         'Copy the Chart
          Chrt.Copy


 Next Chrt

Keep in mind, all this is doing is looping through each chart in our sheet and copying it. At this point, we haven’t specified where to paste it.

Step Six: Create The Slide

At this point we still don’t have a slide in our presentation, so we need to create one. To create a slide, we need to go into our PPTPres object, then the Slides Collection, and call the Add method in order to create a slide. There are two parameters we can pass through. The first specifies the Slide index or the position of our slide in the presentation.

This is where I’m going to pass through the slide index that I declare up above. Now because I set the value to one when we are the first loop we will be creating our first slide. Now we will have to create a way to change this value as we go to the next loop but that’s down below.

The next parameter, PpSlideLayout, is the next parameter that we need to pass through. This parameter is a constant that specifies the layout of the slide. In this case, we just want a blank slide layout so we will pass through ppLayoutBlank. Here is the code for this section:

'Loop through all the CHARTOBJECTS in the ACTIVESHEET.
 For Each Chrt In ActiveSheet.ChartObjects
        
       'Copy the Chart
        Chrt.Copy
        
       'Create a new slide in the Presentation,and set the layout to blank
        Set PPTSlide = PPTPres.Slides.Add(SldIndex, ppLayoutBlank)


 Next Chrt

Step Eight: Paste The Chart

To Paste the chart we will go in our PPTSlide object, into the Shapes collection and call the PasteSpecial Method or if you want you can call the more general Paste method. In this example, I use the PasteSpecial method but I’ll provide code that does a regular paste. If we use the Paste special method we need to pass through the DataType parameter that will specify how we want to paste it. In this example, I paste it as an OLEObject which is basically an embedded Excel chart. Now because I chose an OLEObject I can actually specify whether I want it linked back to my workbook. I chose to link it. Here is the code:

'Loop through all the CHARTOBJECTS in the ACTIVESHEET.
 For Each Chrt In ActiveSheet.ChartObjects
        
       'Copy the Chart
        Chrt.Copy
        
       'Create a new slide in the Presentation,and set the layout to blank
        Set PPTSlide = PPTPres.Slides.Add(SldIndex, ppLayoutBlank)


       'Paste as OLEObject and it is linked.
        PPTSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoTrue
       'PPTSlide.Shapes.Paste MORE GENERAL PASTE METHOD


 Next Chrt

Step Nine: Increment Our Slide Index Handler

Now comes the import part, remember how we want our second chart to go on the second slide, the third chart on the third slide and so on? Well in order to do this we need to increment our Slide Index handler in our code. Why do we have to do this? Well, when we increment it, we are basically increasing its value by one on each loop so when we go to pass through our slide index handler to create our new slide it is the correct value. By the correct value, I mean that it will add a new slide to the right position in our presentation. Here is how we increment our Slide Index Handler:

'Loop through all the CHARTOBJECTS in the ACTIVESHEET.
 For Each Chrt In ActiveSheet.ChartObjects
        
       'Copy the Chart
        Chrt.Copy
        
       'Create a new slide in the Presentation,and set the layout to blank
        Set PPTSlide = PPTPres.Slides.Add(SldIndex, ppLayoutBlank)


       'Paste as OLEObject and it is linked.
        PPTSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoTrue
       'PPTSlide.Shapes.Paste MORE GENERAL PASTE METHOD


       'Increment index so that we paste the next chart on the new slide that is added.
        SldIndex = SldIndex + 1


 Next Chrt

Here is the full code using early binding

Sub ExportChartsToPowerPoint_SingleWorksheet()


   'Declare PowerPoint Variables
    Dim PPTApp As PowerPoint.Application
    Dim PPTPres As PowerPoint.Presentation
    Dim PPTSlide As PowerPoint.Slide
    Dim SldIndex As Integer
        
   'Declare Excel Variables
    Dim Chrt As ChartObject
            
   'Create new PowerPoint Application & make it visible.
    Set PPTApp = New PowerPoint.Application
        PPTApp.Visible = True
    
   'Create a new presentation in the PowerPoint application.
    Set PPTPres = PPTApp.Presentations.Add
    
   'Create an index handler for slide creation.
    SldIndex = 1
    
   'Loop through all the CHARTOBJECTS in the ACTIVESHEET.
    For Each Chrt In ActiveSheet.ChartObjects
        
       'Copy the Chart
        Chrt.Copy
        
       'Create a new slide in the Presentation, set the layout to blank, and paste chart on to the newly added slide.
        Set PPTSlide = PPTPres.Slides.Add(SldIndex, ppLayoutBlank)
            PPTSlide.Shapes.Paste
        
       'Increment index so that we paste the next chart on the new slide that is added.
        SldIndex = SldIndex + 1
    
    Next Chrt
    
End Sub