Copy Excel Chart in Powerpoint – Macro VBA pastespecial

How to Copy Excel Chart in PowerPoint Slides using VBA Macro – Step by Step Tutorial

Requirement:

We need to copy several charts from Excel in Powerpoint slides. And you want to skip the manual part ? Yes, you are in right place and this simple vba macro automation will do the task for you. Supposedly if you’ve charts present in many worksheets in your Excel, we can easily copy each charts and it can be added as new Slides in Powerpoint.

Prerequisite:

Add “Microsoft PowerPoint Object Library” through Tools->References to access PowerPoint related objects in VBA Macro.

VBA Code to Copy Excel Chart in PowerPoint Slides:

Copy the below code in the Excel containing Charts and Run it. You can see all the charts getting copied as new Slides in “Sample.pptx”. You will need to enable the macro first.

    Dim ppApp As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    Dim ppSlide As PowerPoint.Slide
    Dim Xchart As Excel.ChartObject
    Dim currentSheet As Worksheet
    
    Dim slideCount As Long        
    slideCount = 0
       
    'Start the PowerPoint presentation - Look for existing instance
    On Error Resume Next
    Set ppApp = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
    
    If ppApp Is Nothing Then
        Set ppApp = New PowerPoint.Application
        ' Make it visible.
        ppApp.Visible = True
    End If
    
    On Error Resume Next
    'can add the full file path here
    Set ppPres = ppApp.Presentations.Open("Sample.pptx")
    
    'If no file is found, create new PPT file namely 'Sample.pptx' and save it
    If (ppPres Is Nothing) Then
            Set ppPres = ppApp.Presentations.Add(msoTrue)
            ppPres.SaveAs ("Sample.pptx")
    End If
       
    For Each currentSheet In ThisWorkbook.Sheets
        'Locate Excel charts to paste into the new PowerPoint presentation
        For Each Xchart In currentSheet.ChartObjects
        
            slideCount = ppApp.ActivePresentation.Slides.Count
            ppApp.ActivePresentation.Slides.Add slideCount + 1, ppLayoutBlank
                
            ppApp.ActiveWindow.View.GotoSlide slideCount + 1
            Set ppSlide = ppApp.ActivePresentation.Slides(slideCount + 1)
            
            'Copy each Excel chart and paste it into PowerPoint as an Shapes
           currentSheet.Activate
            Xchart.Select
            ActiveChart.ChartArea.Copy
            
            'paste the chart
            ppSlide.Shapes.PasteSpecial(DataType:=ppPasteShape).Select
        
            'Adjust the slide position for each chart slide in PowerPoint. Note that you can adjust the values to position the chart on the slide to your liking
            ppApp.ActiveWindow.Selection.ShapeRange.Left = 100
            ppApp.ActiveWindow.Selection.ShapeRange.Top = 150
            
            ppPres.Save
        Next
    Next
    
    On Error Resume Next
    AppActivate ("Microsoft PowerPoint")
    
    Set ppSlide = Nothing
    Set ppApp = Nothing
    Set ppPres = Nothing

Code Explanation:

We majorly use 3 components here to copy Excel Chart in Powerpoint Slides in the below VBA Macro,

1. Excel.ChartObject

2. Powerpoint object

3. VBA PasteSpecial

Step 1:

First lets understand the PowerPoint Application object and how to initialize it. You can check the code prior to For loop to sync it with description.

• It first checks for any existing Powerpoint instance and gets the handle to it as “PowerPoint.Application”
• If no instances are found, new Powerpoint instance is created and it is made visible. At this point, you can see the PowerPoint app running in your machine.
• Now try to open the file name ‘Sample.pptx’. If the file is present in the current folder of the Excel, it gets opened. And we get the object assigned to “PowerPoint.Presentation”.
• If no file is present, a new file is created and saved as “Sample.pptx” so that VBA macro can copy the Excel charts to this Powerpoint file.

Step 2:

We can access the chart objects defined as “Excel.ChartObject” and it can be derived from worksheet’s  chart objects (currentSheet.ChartObjects ). We loop through each sheet and obtain the chart objects from it. Code Xchart.Select and ActiveChart.ChartArea.Copy does the copy magic for us.

Please re-read the For loop structure to understand this better.

Step 3: 

Lets find out how to create new slide and activate it. And then we use VBA pastespecial to copy the charts from Excel to Powerpoint Slide.

• We first go to the end of powerpoint slide and add new slide using “ActivePresentation.Slides.Add” method. Please note that there are lot of layouts available for Powerpoint slide. And here, we are creating a blank layout using ‘ppLayoutBlank’.
• We have used “Shapes.PasteSpecial” method with argument as “ppPasteShape”. This way, charts gets pasted as charts and we can do any formatting related to charts like Pie charts, 2D-3D, color formatting, etc. You can explore the possible argument types and can play around with it.
• Finally we save the changes using Save method

We have successfully learnt how to copy Excel Chart in Powerpoint Slides through VBA Macro.