Paste Unlinked Excel Chart to Powerpoint

Question

I'm currently working on a project transferring 50+ groups of Excel Charts to a powerpoint presentation. I have 50+ items that I'm comparing and making 50+ identical charts. The way that I have it set up in my excel workbook is that the chart is always the same chart (ie Chart 2), but by changing a unique ID number, my chart will source from a different area of the worksheet.

In the usual case, I would just copy and paste the chart as a picture.

However, in my case, I also need to get rid of all data labels <10%. I have found a code to do delete <10% datalabels in Powerpoint, but not excel. In order to execute this code, I have to keep the object in "chart" format. Unfortunately, because of how I have set up my charts to be the same chart that can source different data, whenever I change a unique ID number to copy a new chart, my previous charts already copied into Powerpoint "update" themselves and look like the information is sourced from the latest item.

My options right now are 1) Copying and Pasting each item one at a time, running my Data label code on Powerpoint, and then converting everything in that slide into a Picture. This is tedious. 2) Figuring out how to edit Data labels within Excel, and then copying and pasting as an image 3) MOST IDEAL: copy and paste an unlinked chart from Excel to PPT. This allows me to run my Powerpoint <10% formatting code, but the unlinking also allows me to change my excel sheet without messing up my current charts.

Does anyone have a clue on how to copy and paste an unlinked chart from Excel to PPT that is NOT a Picture?

Answer 1

Here goes possible solution for you:

Sub Breaking_links()

Dim CHR As Shape

Set CHR = ActivePresentation.Slides(1).Shapes(3) 'for 3rd chart shape on 1st slide

CHR.Chart.ChartData.BreakLink

End Sub

Short explanation- after you copy a chart into PP you need to break link to data source which present above example.

Moreover, I think you could modify your axis in Excel as easy as you do in PP. If you found that option better please show us your code for PP which would be helpful to provide you some tips for Excel.

Answer 2

If you want to paste an Excel Chart into PowerPoint as an Unliked object but still maintain it as an embedded OLEObject I recommend that you paste it as an OLEObject. The pasting would look like this:

'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.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse

Here is some simple code where you can paste multiple charts, across the workbook, into a PowerPoint Presentation as an OLEObject without a link.

Sub ExportChartsToPowerPoint_MultipleWorksheets()

    ' OVERVIEW:
    ' This script will loop through all the worksheets in the Active Workbook
    ' and copy all the Charts to a new PowerPoint presentation that we create.
    ' Each chart will get their own individual slide and will be placed in the center of it.

    'Declare PowerPoint Variables
    Dim PPTApp As PowerPoint.Application
    Dim PPTPres As PowerPoint.Presentation
    Dim PPTSlide As PowerPoint.Slide
    Dim PPTShape As PowerPoint.Shape
    Dim SldIndex As Integer

    'Declare Excel Variables
    Dim Chrt As ChartObject
    Dim WrkSht As Worksheet

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

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

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

    'Loop throught all the Worksheets in the Worksheets Collection.
    For Each WrkSht In Worksheets

        'Loop through all the CHARTOBJECTS in the ACTIVESHEET.
        For Each Chrt In WrkSht.ChartObjects

            'Copy the Chart
            Chrt.Chart.ChartArea.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.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse

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

        Next Chrt

    Next WrkSht

End Sub

Now, this is actually some code that I go over in one of my YouTube videos so if you want to walk through this entire code I encourage you to visit the link below.

https://youtu.be/DOaBtYMCCEM

FULL DISCLOSURE THIS IS MY PERSONAL YOUTUBE CHANNEL.