Argthtjtr

I have a problem with my code when I'm trying to copy some data from excel to PowerPoint using Excel VBA.

The problem is that it sometimes work properly and with no error but sometimes its crashes and stops during the run.

Sub Test()





Set PowerPointApp = CreateObject("PowerPoint.Application")

Set ppApp = New powerpoint.Application

ppApp.Visible = True

DestinationPPT = "C:UsersSaeedDesktopedit vbatest.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:UsersSaeedDesktopedit vba" & FileName, ppSaveAsPDF

ppPres.Close

ppApp.Quit

Set ppt = Nothing

I skipped the dim parts and some unimportant ones.

But here is the problem : it always crashes in

ppApp.Windows(1).View.Paste

and I don't know how to fix it since it sometimes runs perfect and sometimes gives me error on that part !
I tried to use On error Goto but nothing changed.
It would be appreciated to help me out in this wierd problem.

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

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:UsersSaeedDesktopedit vbatest.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:UsersSaeedDesktopedit vba" & Filename, ppSaveAsPDF

PPTPres.Close

PPTApp.Quit



'Release Objects From Memory

Set PPTApp = Nothing

Set PPTPres = Nothing

Set PPTShape = Nothing



End Sub