Error on .Paste while copying from Excel to PowerPoint

Question

I'm trying to copy data from Excel to PowerPoint using Excel VBA.

Sometimes it crashes and stops during the run.

Sub Test()

Set PowerPointApp = CreateObject("PowerPoint.Application")
Set ppApp = New powerpoint.Application
ppApp.Visible = True
DestinationPPT = "C:\Users\Saeed\Desktop\edit vba\test.pptx"
Set ppPres = PowerPointApp.Presentations.Open(DestinationPPT)
Sheets("Slide3").Activate
Sheets("Slide3").Range("A2").Select
Selection.Copy
ppApp.Activate
ppPres.Slides(3).Select
ppApp.Windows(1).View.Paste
Set shp = ppPres.Slides(3).Shapes(ppPres.Slides(3).Shapes.Count)
shp.Left = 17
shp.Top = 90
ppApp.Windows(1).Selection.Unselect

ppPres.SaveAs "C:\Users\Saeed\Desktop\edit vba\" & FileName, ppSaveAsPDF
ppPres.Close
ppApp.Quit
Set ppt = Nothing

I skipped the dim parts and some unimportant ones.

It crashes in

ppApp.Windows(1).View.Paste

I don't know how to fix it since it sometimes runs perfectly.

I tried to use On Error Goto but nothing changed.

Answer 1

My first guess is that you should not paste it into the view but into the slide instead.

ppPres.Slides(3).shapes.paste

Cheers Jens

Answer 2

One thing I wanted to point out is in your code you were jumping between late and early binding. I don't know if this was intentional or not, but ideally, you would only want to choose one. In my solution, I am assuming you want early binding.

Now the other thing I would recommend you do is to make sure you declare all of our variables so that way you can write your code a little more concisely and it's easier to know which object we are working with.

The reason you are having the problem could be a couple of different issues but one of them is possibly related to the clipboard. The reason I suspect this is that you're saying the error is sporadic, this usually is an indication of the clipboard error. Lucky for us there are a couple of solutions we can implement. My go-to solution is to pause the Excel application for one or two seconds to ensure the information makes it to the clipboard. This solution usually fixes 95% of the errors related to the clipboard.

With that said, it won't work 100% of the time. As strange as this may sound we can still have situations where the information disappears from the clipboard.

Also if you're new to Excel VBA & PowerPoint VBA working in tandem I have some YouTube videos that go over this topic. Feel free to check them out if you think you will want to do more complicated scripts.

https://www.youtube.com/playlist?list=PLcFcktZ0wnNlFcSydYb8bI1AclQ4I38VN

Try out this code and let me know what you get:

Sub Test()

'Declare Variables
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTShape As PowerPoint.Shape

'Create a new instance of PowerPoint
Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True

'File Path
DestinationPPT = "C:\Users\Saeed\Desktop\edit vba\test.pptx"

'Open the File
Set PPTPres = PPTApp.Presentations.Open(DestinationPPT)

'Copy Range "A2" on the sheet.
Sheets("Slide3").Activate
Sheets("Slide3").Range("A2").Copy

'Pause the Excel Applicaiton for one second. This is for stability issues that may arise.
Application.Wait Now() + #12:00:01 AM#

'Paste the Range on the Slide
PPTPres.Slides(3).Shapes.Paste

Set PPTShape = PPTPres.Slides(3).Shapes(PPTPres.Slides(3).Shapes.Count)
    PPTShape.Select

'Set Dimensions of Shape
With PPTShape
    .Left = 17
    .Top = 90
End With

'Save & Close the file
PPTPres.SaveAs "C:\Users\Saeed\Desktop\edit vba\" & Filename, ppSaveAsPDF
PPTPres.Close
PPTApp.Quit

'Release Objects From Memory
Set PPTApp = Nothing
Set PPTPres = Nothing
Set PPTShape = Nothing

End Sub