So, I’ve spent the last couple of weeks trying to get that one right.
Piecing together all the hints from stackoverflow and the rest, I find that I have to use the ExecuteMso method available in PP2010 and later, and use Doevents to give PP time to act in order to avoid raising errors.
In this code, mPPAppObject is the Powerpoint Application object accessed from Excel VBA eg
Set mPPAppObject = GetObject(, "PowerPoint.Application")
Assuming we start with
rng.Copy then if the paste is to be a table then
for i=1 to 500:Doevents :next ' 5 hundred, about 10ms timing
‘Otherwise get a Clipboard Error -2147188160 Shapes (unknown member) : Invalid request. Clipboard is empty or contains data which may not be pasted here.
mPPAppObject.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting" ' 2010+
for i=1 to 5000:Doevents :next ' 5 thousand, about 100ms
‘Otherwise calling code testing the slide.shapes collection does not see the pasted shape yet, or it does not see that the shape has a table (oShape.HasTable)
Set oShape = mPPSlide.Shapes(mPPSlide.Shapes.Count)
If we want some other paste format, then it’s a bit easier:
Set oShape = mPPSlide.Shapes.PasteSpecial(PasteDataType)
‘Where PasteDataType can be
‘ 0 ppPasteDefault ‘ truncates to correspond to visible on worksheet window
‘ 2 ppPasteEnhancedMetafile pastes correctly with full width
‘ 3 ppPasteMetafilePicture ‘ same as default, *truncated if too wide*
‘ 7 ppPasteText
‘ 8 ppPasteHTML ‘ does not work, use mPPAppObject.ActiveWindow.View.Paste
In PP 2013 I sometimes found that more than one shape would be pasted, so:
' oShape.Count property does not exist for correctly pasted shape. So use errtrapped function
If ObjectCount(oShape) > 1 Then ' handle pp 2013 bug
'Debug.Print ">1 shape pasted: "; TypeName(oShape); oShape.Count ' ShapeRange 2
Set oShape = oShape(1)
Function ObjectCount(obj As Object) As Long
If obj Is Nothing Then
ObjectCount = 0
ObjectCount = 1
On Error Resume Next
ObjectCount = obj.Count
'could do Err.Clear here if you like
What a kludge to get something done that should be easily accessible from the COM model.