Excel VBA - pasted object into Powerpoint loses selection for manipulation

Question

Just to let you all know, I'm a beginner to VBA and I'm using Excel and PowerPoint 2013.

The aim: I'm writing code in Excel VBA to paste a chart from Excel into PowerPoint. Then when it's pasted, move it to a set place within the slide.
The code I used: The framework of the code I started with is in http://peltiertech.com/Excel/XL_PPT.html#chartppt, the part called 'Paste the Active Excel Chart into the Active PowerPoint Slide (Early Binding)'. The main difference is around the 'Paste' part, this is instead a 'PasteSpecial' and my code is as follows:

.

Charty.CopyPicture

ppApp.ActivePresentation.Slides(SlideNumber).Shapes.PasteSpecial (ppPasteEnhancedMetafile)

' Setting the correct position on the slide

ppApp.ActiveWindow.Selection.ShapeRange.Top = ChartTop
ppApp.ActiveWindow.Selection.ShapeRange.Left = ChartLeft

(Note. ChartTop and ChartLeft have been defined earlier)

The issues: I'm aware that after the PasteSpecial it doesn't have a select statement. However, (1) When I run the above code I get a runtime error '-2147188160' saying the Selection.ShapeRange is an invalid request (in the 'ChartTop' line), ie. it isn't selected so it can't be moved. (2) When I add a select statement to the 'Paste.Special' line I get an 'Object required' error on the new 'PasteSpecial' line, ie. it doesn't recognise it as a paste select statement.
The odd part: When I run the same macro on other people's computers using the exact same Excel and PowerPoint files it works (without the select statement in the 'PasteSpecial' line). I've looked at 'Tools' => 'References', and both of our computer have the same settings.
The questions: Is there a setting that I have overlooked on my computer which prevents the code from working? Is there a way after I've pasted it to reselect it before it is moved (I should tell you that the macro is to paste in many graphs into ppt simultaneously)? How come for other people at my work it works on their computers without the 'select' part of the PasteSpecial statement? How come when I use the Shapes.PasteSpecial (ppPasteEnhancedMetafile).Select statement, it doesn't recognise it as an object?

I hope that's clear. Thanks in advance.

Steve Rindsberg Steve Rindsberg · Accepted Answer · 2014-01-15T04:23:00

Dim oSh as Shape

Set oSh = ppApp.ActivePresentation.Slides(SlideNumber).Shapes.PasteSpecial (ppPasteEnhancedMetafile)(1)

Now you don't need to select anything (which is best avoided whenever possible). Just manipulate the pasted shape, oSh, directly.

The curious syntax (the (1) at the end) is because Shapes.Paste or .PasteSpecial returns a shaperange, not a shape; the (1) causes it to assign the first shape in the range to the oSh variable. Since there's only one shape in the range, that suits us just fine.

Excel VBA - pasted object into Powerpoint loses selection for manipulation

1 Answers