VBA: Paste excel range to powerpoint placeholder without using .Select

Question

I want to paste a named excel range to a content placeholder in powerpoint in a custom layout. I'm currently using code like this

ranger.Copy
currentPPT.ActiveWindow.View.GotoSlide ppt.slides.Count
activeSlide.shapes("Picture").Select msoTrue
ppt.Windows(1).View.PasteSpecial (ppPasteEnhancedMetafile)

It usually works but sometimes fails inexplicably. I have seen elsewhere on this site, here for example, saying to avoid using .Select method. Instead use something like

Dim oSh As Shape
Set oSh = ActivePresentation.Slides(9).Shapes.PasteSpecial(ppPasteEnhancedMetafile)(1)

However, I can't figure out how to use the second method to copy straight to a content placeholder. Is that possible?

Edit, regarding Shai's suggestion. Current code is

For ii = activeSlide.shapes.Count To 1 Step -1
If activeSlide.shapes.Item(ii).Name = "Picture" Then
    shapeInd = ii
    Exit For
End If
Next ii

Set oSh = activeSlide.shapes.PasteSpecial(2, msoFalse)(shapeInd)

The "Picture" shape is a "Content" Placeholder. The other two shapes are text boxes.

take a look at my answer and code below, let me know if it works as you intended — Shai Rado
– Shai Rado, Commented Mar 9, 2017 at 18:02

Shai Rado · Accepted Answer · 2017-03-09 17:56:38Z

0

The code below will do as you mentioned in your post.

First it creates all the necessary PowerPoint objects, including setting the Presentation and PPSlide.

Afterwards, it loops through all Shapes in PPSlide, and when it finds the Shape with Name = "Picture" it retrieves the index of the shape in that sheet, so it can Paste the Range object directly to this Shape (as Placeholder).

Code

Option Explicit

Sub ExporttoPPT()

Dim ranger          As Range
Dim PPApp           As PowerPoint.Application
Dim PPPres          As Presentation
Dim PPSlide         As Slide
Dim oSh             As Object

Set PPApp = New PowerPoint.Application
Set PPPres = PPApp.Presentations("PPT_TEST") ' <-- change to your open Presentation
Set PPSlide = PPPres.Slides(9)

Set ranger = Worksheets("Sheet1").Range("A1:C5")    
ranger.Copy

Dim i As Long, ShapeInd As Long

' loop through all shapes in Slide, check for Shape Name = "Picture"
For i = PPSlide.Shapes.Count To 1 Step -1
    If PPSlide.Shapes.Item(i).Name = "Picture" Then
        ShapeInd = i '<-- retrieve the index of the searched shape
        Exit For
    End If
Next i

Set oSh = PPSlide.Shapes.PasteSpecial(2, msoFalse)(ShapeInd) ' ppPasteEnhancedMetafile = 2

End Sub

answered Mar 9, 2017 at 17:56

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

gabagool Over a year ago

I'm getting an error on the very last line. ShapeRange.Item : Integer out of range. 2 is not in Index's valid range of 1 to 1. I'm not sure why that's happening. I've verified that activeSlide.shapes.Count is 3

Shai Rado Over a year ago

@drj3122 which line ? how many shapes do you have in Slide 9 ? does ShapeInd has a value ? can you try in debug mode ?

gabagool Over a year ago

Set oSh = activeSlide.shapes.PasteSpecial(2, msoFalse)(shapeInd) 3 shapes on the slide. shapeInd is the proper value (2)

Shai Rado Over a year ago

@drj3122 is oSh defined As Object ? or As Shape ?

gabagool Over a year ago

defined As Object

|

Collectives™ on Stack Overflow

VBA: Paste excel range to powerpoint placeholder without using .Select

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related