How can I copy an image from excel to a Shape of PPT?

Question

I try this code, to copy from excel to ppt:

  Dim presentation As Object
  Set ppt = CreateObject("PowerPoint.Application")
  Set presentation = ppt.Presentations.Open2007("D:\temp.pptx", MsoTriState.msoFalse, MsoTriState.msoFalse, MsoTriState.msoTrue)

 Dim oSlide As Object        
 Set oSlide = presentation.Slides(7)
 Dim oSheet As Worksheet
 Set oSheet = ThisWorkbook.Sheets(2)
 Dim oImageOb As Object
 Set oImageOb = oSheet.Shapes(1)
 oImageOb.Copy

 oSlide.Shapes.PasteSpecial DataType:=2

But PPT exits after the execution of PasteSpecial .

`oSlide` is not defined. Please include the relevant code part or a full [mcve]. — Pᴇʜ, May 16 '19 at 12:39
Do you have a reference to PowerPoint object model in the VB project? If not, `ppPasteEnhancedMetafile` is undefined (you should use `Option Explicit` which would warn you of such potential error as undefined variants). — David Zemens, May 16 '19 at 12:54
By "PPT exits after the execution of `PasteSpecial`, do you mean that the PowerPoint Application closes? — Prebsus, May 16 '19 at 13:00
And is there a reason you're using the `.Open2007` method rather than the `.Open` method? — David Zemens, May 16 '19 at 13:11
When I take that code and put it in a `Sub Test()`, I get exactly what you expect, hence I am unable to reproduce your issue. What version of Office are you on? — Prebsus, May 16 '19 at 13:11
I also get exactly what you expect, using late binding, with and without the explicit declartaion of the `ppt` variable and with/without `Option Explicit`. — David Zemens, May 16 '19 at 13:18

score 1 · Accepted Answer · answered May 16 '19 at 13:04

1

Not sure if it makes a difference, but I like to explicitly state what kind of object I refer to when using VBA from Excel to PowerPoint:

Dim presentation As PowerPoint.Presentation
Set ppt = New PowerPoint.Application
Set presentation = ppt.Presentations.Open2007("D:\temp.pptx", MsoTriState.msoFalse, MsoTriState.msoFalse, MsoTriState.msoTrue)

Dim oSlide As Object        
Set oSlide = presentation.Slides(7)
Dim oSheet As Worksheet
Set oSheet = ThisWorkbook.Sheets(2)
Dim oImageOb As Object
Set oImageOb = oSheet.Shapes(1)
oImageOb.Copy

oSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile

This code works fine for me (just replacing the location of the PPT-file, of course). And by "works", I mean the figure/image/shape is copied from excel to powerpoint, without powerpoint closing afterwards

answered May 16 '19 at 13:04

Prebsus

695
9
17

you can try some times. – D T May 16 '19 at 13:11
1

(Note: this shouldn't make a difference) An `Object` assigned the return of `Presentations.Open/Open2007` will return an instance of the `PowerPoint.Presentation` class, regardless of whether you're using late- or early-binding, whether you've added an explicit reference to the PPT object model. – David Zemens May 16 '19 at 13:12
@DavidZemens Agreed - but as I was unable to reproduce the OPs issue, I figured it might be worth a shot anyways...but you're of course right – Prebsus May 16 '19 at 13:13
Tested & confirmed this works with late-binding also. – David Zemens May 16 '19 at 13:17
I'm sorry. It is working. I run PasteSpecial in debug. So it had exit PPT app. – D T May 16 '19 at 13:19
I have a the problem. Can i paste image into a Shape of PTT? – D T May 16 '19 at 13:22
@DT, yes you can do that. But you need to reference the shape in PPT. This can be done both by Index and Name, however, I would recommend going by Name as it is a bit more legible – Prebsus May 16 '19 at 13:24
Shape object not support PasteSpecial: i try oSlide.Shapes(7).PasteSpecial DataType:=2 – D T May 16 '19 at 13:26
Yeah, so you can't paste directly into it like that. My favourite solution is to get the details of the Shape you want to paste into, then delete that shape, and copy in and assign the details (left, top, width, height) to the new shape. – Prebsus May 16 '19 at 13:29
I realise my comment above was a bit unclear - what kind of shape are you trying to 'paste into' or replace? – Prebsus May 16 '19 at 13:30
OK.I know, i only confirm it. can paste direct to a shape or not. – D T May 16 '19 at 13:34
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193470/discussion-between-prebsus-and-d-t). – Prebsus May 16 '19 at 13:41

Steve Rindsberg · Answer 2 · 2019-05-16T13:40:04.897

This seems to be a timing problem. It bites some people/some PCs and not others. You paste the shape and then try to do something with it while PPT is still processing the request, so the "do something wit it" part fails.

The usual workaround is to give it a little extra time and try a few extra times:

In the Declarations section of your module, include this:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

That's for 32-bit PowerPoint; getting it to work in 64-bit PPT or in both is possible, but a subject for a different thread.

Then in the part of your sub that pastes the shape, try pasting several times with a pause between each try:

Dim oShp as Shape
Dim x as Long

On Error Resume Next

For x = 1 to 3 ' or whatever number you want to try
  Set oShp = oSlide.Shapes.PasteSpecial DataType:=2
  Sleep(1000)  ' Adjust this as needed
  If Not oShp is Nothing Then
    Exit For
  End If
Next

If oShp is Nothing Then
  ' It didn't work.
  ' Do whatever you need to do to recover
End If

On Error GoTo YourRegularErrorHandler
' Which you should add

David Zemens · Answer 3 · 2019-05-16T14:29:54.353

In order to paste the image into the specified shape in PowerPoint, there are some caveats:

The Shape must be of a type that allows images, such as certain content placeholders. You cannot insert images into text boxes, chart placeholders, etc.
The Shape must be Selected. While we're accustomed to telling people to avoid using Select or Activate in Excel VBA, in PowerPoint and Word however, certain actions can only be performed when the object is in view and/or selected. In order to Select the shape, we need to Select the slide.

I've cleaned up your procedure by moving the variable declarations to the top, and modified the path/slide indices etc. I've created a new variable pptShape which we'll use to handle the specific shape instance on the slide.

Note that I've changed the path and slide/shape indices.

Option Explicit

Sub foo()
Dim ppt As Object 'PowerPoint.Application
Dim oSlide As Object 'PowerPoint.Slide
Dim pptShape As Object 'PowerPoint.Shape
Dim oImageOb As Object
Dim oSheet As Worksheet
Dim pres As Object 'PowerPoint.Presentation

Set ppt = CreateObject("PowerPoint.Application")
Set pres = ppt.Presentations.Open2007("c:\debug\empty ppt.pptx", MsoTriState.msoFalse, MsoTriState.msoFalse, MsoTriState.msoTrue)
Set oSlide = pres.Slides(3)

Set oSheet = ThisWorkbook.Sheets(1)  ' ## MODIFY AS NEEDED
Set oImageOb = oSheet.Shapes(1)      ' ## MODIFY AS NEEDED
oImageOb.Copy

Set pptShape = oSlide.Shapes(1)      ' ## MODIFY AS NEEDED

'## to preserve aspect ratio and prevent stretching/skewing image:
pptShape.Width = oImageOb.Width
pptShape.Height = oImageOb.Height

' ## Select the slide
oSlide.Select
' ## Selct the shape
' ## NOTE: This shape MUST be of a type that contains a picture frame, otherwise
'          an error will occur
pptShape.Select

' ## All of the following methods work for me:
'ppt.CommandBars.ExecuteMso "PasteJpeg"
'ppt.CommandBars.ExecuteMso "PasteBitmap"
'ppt.CommandBars.ExecuteMso "PasteAsPicture"
ppt.CommandBars.ExecuteMso "Paste"


End Sub

Here is my Excel sheet with an image:

And the output, slide with the image pasted into the appropriate Image Placeholder:

A [placeholder](https://learn.microsoft.com/en-us/office/vba/api/powerpoint.placeholders) is a specific type of shape in the PPT object model that's part of the presentation's SlideMaster Layout. It's nominally a "rectangle" (i.e., it is usually a quadrangle/parallelogram with four sides and 90 degree angles), but it's **NOT** the "rectangle" you'd get from the Insert > Shapes > Rectangle option. That type of shape can be *filled* with a picture from the clipboard or file location, but it's not the same thing and can't implement it the same way. — David Zemens, May 17 '19 at 03:33
thank you. i had can add a placeholder. but after paste image to placeholder. How can fit image to size of placeholder or fit placeholder to image? — D T, May 17 '19 at 03:39
Image after paste to a placeholder ,it is not correct If image on excel had crop — D T, May 17 '19 at 03:43
`pptShape.Width = oImageOb.Width` and the next line with `.Height` *should* do that, no? — David Zemens, May 17 '19 at 11:44

How can I copy an image from excel to a Shape of PPT?

3 Answers3