RunTime Automation Error on copying Excel pictures into PowerPoint

[Glassford]

Hi, I have a macro that generates PowerPoint slides which a bunch of different things from Excel. I have a total of about 50 Slides to generate, often at around 20 slides the code will crash and give a runtime error. When it crashes its always got something to do with this section of the code where it copies and pastes the picture from Excel to PowerPoint.

The error either occurs on the .CopyPicture or the .Paste lines.

Currently I've tried the following and still have errors:

• CutCopyMode = False right after the paste.
• ScreenUpdating both True and False
• WindowMinimized both True and False

The only thing I can think of is it being a memory issue, all my variables are either inside functions or subs and the only objects that are set in the main sub are all set = nothing at the end of the sub.

Any help would be great. Thanks

[TABLE="width: 1000"]
<tbody>[TR]
[TD]' This Sub copies a picture from Excel, pastes to PowerPoint, resizes and re positions the selection.
Sub CopyToPowerPoint(PictureToCopy As String, TopPosition As Double, LeftPosition As Double, HeightSize As Double, WidthSize As Double)
' If only resizing an image, skips the copy/paste
If PictureToCopy = "ResizeOnly" Then
GoTo SkipCopy
End If
wb.Sheets("Dashboard").ChartObjects(PictureToCopy).CopyPicture
ppPres.Slides(1).Shapes.Paste
SkipCopy:
ppApp.Visible = msoTrue
With ppApp.Windows(1).Selection
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Top = TopPosition * cmtopixel
.ShapeRange.Left = LeftPosition * cmtopixel
.ShapeRange.Width = WidthSize * cmtopixel
.ShapeRange.Height = HeightSize * cmtopixel
End With

End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[areed1192]

If you have the full code, I'd be happy to look at it for you and determine if there is a bigger issue at hand. However, based on what you've described so far it sounds like a clipboard error. This type of error has nothing really to do with your code but it's related to the overall Office application. What I recommend to do is to pause the script for about 1 to 2 seconds after copying the image. This will make sure that Excel has enough time to copy the image to the clipboard. I rewrote your code to include the pause:

Sub PasteToPPT()




' This Sub copies a picture from Excel, pastes to PowerPoint, resizes and re positions the selection.
Sub CopyToPowerPoint(PictureToCopy As String, TopPosition As Double, LeftPosition As Double, HeightSize As Double, WidthSize As Double)


' If only resizing an image, skips the copy/paste
If PictureToCopy = "ResizeOnly" Then
   GoTo SkipCopy
End If


'Copy the picture
wb.Sheets("Dashboard").ChartObjects(PictureToCopy).CopyPicture


'Pause the Excel Application for 2 seconds
Application.Pause Now() + #12:00:02 AM#


'Paste the shape.
PPPres.Slides(1).Shapes.Paste


SkipCopy:


ppApp.Visible = msoTrue


'Resize the shape.
With ppApp.Windows(1).Selection
    .ShapeRange.LockAspectRatio = msoFalse
    .ShapeRange.Top = TopPosition * cmtopixel
    .ShapeRange.Left = LeftPosition * cmtopixel
    .ShapeRange.Width = WidthSize * cmtopixel
    .ShapeRange.Height = HeightSize * cmtopixel
End With


End Sub