Shapes.PasteSpecial works in Office 2010, but not in 2013

Hello - a routine I use to copy Excel charts to PowerPoint slides works fine in Office 2010, but in Office 2013 gets an "Object Required" error on the following line of code:  

pptSlide.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile).Select

I have seen a few posts on this, but no solutions. Does anybody know the fix for this?

Thanks
mlagrangeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
ProfessorJimJamMicrosoft Excel ExpertCommented:
you must have decalred the pptSlide as Object, instead of ShapeRange
Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
It could be because the paste operation isn't working prior to the Select method being used.

Try this to separate the two parts of the operation:

pptSlide.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile)
pptSlide.Shapes(1).Select

Open in new window


If it fails on the first line, IIRC the PasteSpecial works differently in VBA in Excel and PowerPoint and depending on which app is hosting the code, you may need to adapt.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

mlagrangeAuthor Commented:
Hello - sorry for leaving this hanging; I had to go deal with other projects.

I've tried the various things suggested in the articles you point to, but my situation is evidently different.

I hesitate to go into too much detail, because it's such a swamp. But here goes...

I'm an Access developer, and reasonably familiar with the Access object model, but not so much with Excel, and even less so with PowerPoint. I relied heavily on the kindess of EE responders.

This routine runs from an xlsm that contains a worksheet of safety awards by employee, that carry 3 levels of the emp's organizational hierachy ("L1", "L2", "L3"), and award descriptions for category and type. The xlsm charts these awards out into 3 pivot charts for award counts by Lx, with respective award categories, and award types.

The routine to copy & paste these 3 charts to PowerPoint actually iterates through: > the L1, then > each L2 w/in the L1, and then > each L3 w/in each L2. Each iteration copies the count, category and type charts to a new slide for the given Lx.  
 
I pretty much thumb-dicked my way into this arrangement where I have a pptx file I use as a template (a functional template, not an actual template). It has a header slide, and then a boilerplate slide that I've arranged with picture/shape placeholders for each chart. At the end of the routine, it is "Save As"'ed to a new file name.

I have reduced the code below to the first copy/paste of the boilerplate slide to the next new slide, and copying & pasting the first (L1) Award count chart from the xlsm to the placeholder on the pptx. I do it the same way at each Lx level, and each chart. It errors out right at this first L1 copy/paste.

If you're still here, I appreciate it  :-)

Option Explicit

Public Function L1toPP(pptPresent As PowerPoint.Presentation)

    'On Error GoTo PROC_ERR
    
    Dim wksAwrds As Worksheet, pvtAwrds As PivotTable, ptfAwrds As PivotField, ptiAwrds As PivotItem, chtAwrds As Excel.ChartObject
    Dim dblTarget As Double, txtTarget As String
    'Dim wksOutside As Worksheet, pvtOutside As PivotTable, dblOutside As Double, txtOutside As String
                        
    '-- L1 (no iteration through rpt filter needed)
    Set wksAwrds = ThisWorkbook.Worksheets("L1 Chart")
    Set pvtAwrds = wksAwrds.PivotTables("pvtL1")
    
    '-- set the L1 Chart AwardQtr to the selected value on the L2 Chart
    pvtAwrds.PivotFields("AwardQtr").CurrentPage = ThisWorkbook.Worksheets("L2 Chart").PivotTables("pvtL2").PivotFields("AwardQtr").CurrentPage.Name
    
    Set chtAwrds = wksAwrds.ChartObjects(1)
    
    '-- set the 2nd slide into an object variable, for ref'ing the positions & measurements later
    Dim pptSlideTmp As PowerPoint.Slide
    Set pptSlideTmp = pptPresent.Slides(2)
    
    '-- copy the 2nd slide in the ppt template for the next new slide (could have just copied [pptSlideTmp] at this point?
    Dim pptSlideNew As PowerPoint.Slide
    pptPresent.Slides(2).Copy
    pptPresent.Slides.Paste (pptPresent.Slides.Count + 1)
    Set pptSlideNew = pptPresent.Slides(pptPresent.Slides.Count)
    pptSlideNew.Select
    
    '-- set the slide title
    pptSlideNew.Shapes("ttlLvl").TextFrame.TextRange.Text = "GREF"
    
    'Copy the chart and paste it into the PowerPoint as a Metafile Picture
    chtAwrds.Select
    chtAwrds.Chart.ChartArea.Copy
    
    '-- delete the placeholder for the Awards chart
    pptSlideNew.Shapes("picAwrds").Delete
    
    '>>> this is the line that errors out with "Object requried"
    pptSlideNew.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select
    
    Dim pptShpAwrds As PowerPoint.Shape
    Set pptShpAwrds = pptSlideNew.Shapes(pptSlideNew.Shapes.Count)
        
    'Adjust the positioning of the Chart on Powerpoint Slide
    pptShpAwrds.Left = pptSlideTmp.Shapes("picAwrds").Left
    pptShpAwrds.Top = pptSlideTmp.Shapes("picAwrds").Top
    pptShpAwrds.Height = pptSlideTmp.Shapes("picAwrds").Height
    pptShpAwrds.Width = pptSlideTmp.Shapes("picAwrds").Width
    
    '-- similar code continues for award category & type charts...

End Function

Open in new window

Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
When you paste in PowerPoint, it creates a ShapeRange collection so you need to refer to the first Shape within that collection. It's also best not to select shapes unless you really need to as this introduces all kinds of other 'in-view' complexities. Swap the error line for this:

' PowerPoint Library enum ppPasteMetafilePicture = 3 ' Preferred late binding without reference to PowerPoint library
Dim oShpRange As Object, pptShpAwrds as Object
Set oShpRange = pptSlideNew.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture)
Set pptShpAwrds = oShpRange(1)

Open in new window


You can then perform operations on the pasted picture by referring to the oShp object:

    Dim oShpRange As Object, pptShpAwrds as Object ' Preferred late binding without reference to PowerPoint library
    Set oShpRange = pptSlideNew.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture)
    Set pptShpAwrds = oShpRange(1)
    ' No need to select the object (it might not be in view) to work on it but included here in case needed:
    ' pptShpAwrds.Select

    'Adjust the positioning of the Chart on Powerpoint Slide
    With pptSlideTmp.Shapes("picAwrds")
        pptShpAwrds.Left = .Left
        pptShpAwrds.Top = .Top
        pptShpAwrds.Height = .Height
        pptShpAwrds.Width = .Width
    End WIth

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlagrangeAuthor Commented:
YES!  THANK YOU!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.