Error after upgrading to 2013 "Object Required" in the line "activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select"

Question

My code was working in 2007. In 2013 an error is thrown as "Object Required" in the line

activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

How do I modify the existing PPT?

Sub CreatePowerPoint()

'Add a reference to the Microsoft PowerPoint Library by:
'1. Go to Tools in the VBA menu
'2. Click on Reference
'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay

'First we declare the variables we will be using
    Dim newPowerPoint As PowerPoint.Application
    Dim activeSlide As PowerPoint.Slide
    Dim cht As Excel.ChartObject

 'Look for existing instance
    On Error Resume Next
    Set newPowerPoint = GetObject(, "PowerPoint.Application")
    On Error GoTo 0

'Let's create a new PowerPoint
    If newPowerPoint Is Nothing Then
        Set newPowerPoint = New PowerPoint.Application
    End If
'Make a presentation in PowerPoint
    If newPowerPoint.Presentations.Count = 0 Then
        newPowerPoint.Presentations.Add
    End If

'Show the PowerPoint
    newPowerPoint.Visible = True

'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
    For Each cht In ActiveSheet.ChartObjects

    'Add a new slide where we will paste the chart
        newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
        newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
        Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

    'Copy the chart and paste it into the PowerPoint as a Metafile Picture
        cht.Select
        ActiveChart.ChartArea.Copy
        activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select


    'Adjust the positioning of the Chart on Powerpoint Slide
        newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
        newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 125

        activeSlide.Shapes(2).Width = 200
        activeSlide.Shapes(2).Left = 505

    Next

AppActivate ("Microsoft PowerPoint")
Set activeSlide = Nothing
Set newPowerPoint = Nothing

End Sub

Answer 1

I hope you don't mind but I am going to clean up your code a little bit, so it's more concise and easier to read. When I was reviewing your code, I actually was not able to recreate the error that you were specifying so there it is likely that you just had a volatile session of PowerPoint. This does happen on occasion and it doesn't mean anything is wrong with your code. That being said, there are steps we can take to make sure we are explicit in our code so this type of error doesn't happen as frequently.

The first thing I wanted to do in your code is create a PowerPoint.Presentation object this way I could create a reference to the Active Presentation. All I did was add the following to your variable section:

Dim PPTPres As PowerPoint.Presentation

and created a reference to the active presentation later in your code:

'Create a reference to the Active Presentation
Set PPTPres = newPowerPoint.ActivePresentation

You'll find that we can write our code a little more concisely once we create this reference. Next thing I did is I created the slide, set a reference to that slide, and selected the slide so that way when we paste we don't get any errors. We can accomplish this with 2 short lines of code versus three:

'Create a new slide, and set this as the slide we want to work with.
 Set PPTSlide = PPTPres.Slides.Add(PPTPres.Slides.Count + 1, ppLayoutText)
     PPTSlide.Select

Next, I want to copy the chart and then paste it in the slide. I removed the select portion as we create a reference up above. Then I pasted it in the slide and selected the newly pasted object. You were correct to select the object because you were calling the selection method down below which means we have to have something selected in order to work with it.

'Copy the chart and paste it into the PowerPoint as a Metafile Picture
ExcCht.Chart.ChartArea.Copy
PPTSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

Finally, I used With blocks to work with your objects as these allow us to write our code more concisely and avoids us having to repeat certain statements.

'Set the dimensions, of the pasted chart.
With newPowerPoint.ActiveWindow.Selection.ShapeRange
    .Left = 15
    .Top = 125
End With

'Set the dimensions of the text box.
With PPTSlide.Shapes(2)
    .Width = 200
    .Left = 505
End With

In all this is how your code will look now.

Sub CreatePowerPoint()

'First we declare the variables we will be using
Dim newPowerPoint As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSlide As PowerPoint.Slide
Dim ExcCht As Excel.ChartObject

'Look for existing instance
On Error Resume Next
Set newPowerPoint = GetObject(, "PowerPoint.Application")
On Error GoTo 0

'Let's create a new PowerPoint instance, if there is none.
If newPowerPoint Is Nothing Then
   Set newPowerPoint = New PowerPoint.Application
End If

'Make a presentation in PowerPoint, if there is none.
If newPowerPoint.Presentations.Count = 0 Then
   newPowerPoint.Presentations.Add
End If

'Show the PowerPoint
newPowerPoint.Visible = True

'Create a reference to the Active Presentation
Set PPTPres = newPowerPoint.ActivePresentation

'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
For Each ExcCht In ActiveSheet.ChartObjects

    'Create a new slide, and set this as the slide we want to work with.
    Set PPTSlide = PPTPres.Slides.Add(PPTPres.Slides.Count + 1, ppLayoutText)
        PPTSlide.Select

    'Copy the chart and paste it into the PowerPoint as a Metafile Picture
    ExcCht.Chart.ChartArea.Copy
    PPTSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

    'Set the dimensions, of the pasted chart.
    With newPowerPoint.ActiveWindow.Selection.ShapeRange
        .Left = 15
        .Top = 125
    End With

    'Set the dimensions of the text box.
    With PPTSlide.Shapes(2)
        .Width = 200
        .Left = 505
    End With

Next

'Activate the PowerPoint Application.
newPowerPoint.Activate

'Release Objects from Memory
Set PPTSlide = Nothing
Set newPowerPoint = Nothing

End Sub