CODEWITHSUNDEEP
excelvbapowerpoint
D T
D T

Reputation: 3756

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

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 .

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

Upvotes: 1

Views: 373

Answers (3)

David Zemens
David Zemens

Reputation: 53663

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

  1. 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.
  2. 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:

enter image description here

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

enter image description here

Upvotes: 1

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

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

Upvotes: 1

Prebsus
Prebsus

Reputation: 695

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

Upvotes: 1

Related Questions