Copying charts from excel to powerpoint keeping source formatting and embedding the chart

Question

I am currently trying to copy some charts from excel to powerpoint using vba and I am currently copying them as pictures to sever the link to the excel data but it would be really useful if I could copy them as embedded charts I know to use the:

CommandBars.ExecuteMso "PasteExcelChartSourceFormatting"

Method but I can't quite figure out how to go about integrating it into my code in order to use it

Set ppProgram = CreateObject("PowerPoint.Application")
Set PowerPointApp = GetObject(, "PowerPoint.Application")

Set myPresentation = PowerPointApp.Presentations("Filename.pptx")

'Copy Excel Range for Chart 1
WsGraph.ChartObjects("Chart 1").Chart.ChartArea.Copy
Set mySlide = myPresentation.Slides(5)
'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
Set myShapeRange = mySlide.Shapes(46)
myShapeRange.Left = 60
myShapeRange.Top = 143
myShapeRange.ZOrder msoSendToBack
myShapeRange.ZOrder msoBringForward

Can anyone help me with changing my paste special to pasting as an embedded chart?

Evan Evan · Accepted Answer · 2015-07-16T12:17:10

With your Excel and Powerpoint objects the following should work:

'copy the chart from Excel
    xlSheet.ChartObjects(ChartName).Select
    xlSheet.ChartObjects(ChartName).Copy
'Select Slide
    Set mySlide = myPresentation.Slides(Charts(r).SlideName)
    mySlide.Select
'stall to make sure the slide is selected
    For k = 1 To 1000
        DoEvents
    Next k
'paste on selected slide
    PPApp.CommandBars.ExecuteMso ("PasteSourceFormatting")
    PPApp.CommandBars.ReleaseFocus
'sit and wait for changes to be made
    For k = 1 To 5000
        DoEvents
    Next k

The wait loops are in there because my Power Point was running slow and throwing errors.

The only problem with this is that it selects the chart and then the slide so it runs a little slower (and has a flashing screen if you use it in a loop for multiple charts) but when I tried to do it straight I kept getting pastes in the wrong slides and such.

Another issue with this approach is you have no reference to the chart after it is pasted so if you would like to resize/format it it needs to be done right after using PPApp.ActiveWindow.Selection.ShapeRange or something of the sort.

Copying charts from excel to powerpoint keeping source formatting and embedding the chart

1 Answers