Error Handling while copying from Excel to PowerPoint

[mouzi4us]

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:\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.
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.

[rlv]

You appear to be mixing early binding and late binding for powerpoint in the same sub. Why? Usually, only one approach is chosen.


I skipped the dim parts and some unimportant ones.

From the perspective of someone taking the time to help you, they are not all that unimportant, since it means a lot of typing and/or guessing about that missing information if the problem is to be replicated.

My guess is that the problem is coming because you are trying to paste to the window instead of to the slide. Here's a potential alternative.


Sub Test()

Set PowerPointApp = CreateObject("PowerPoint.Application")
PowerPointApp.Visible = True
DestinationPPT = "C:\Users\Saeed\Desktop\edit vba\test.pptx"
Set ppPres = PowerPointApp.Presentations.Open(DestinationPPT)
Sheets("Slide3").Range("A2").Copy
With ppPres.Slides(3)
.Shapes.Paste
Set shp = .Shapes(.Shapes.Count)
shp.Left = 17
shp.Top = 90
End With
ppPres.SaveAs "C:\Users\Saeed\Desktop\edit vba" & Filename, ppSaveAsPDF
ppPres.Close
PowerPointApp.Quit
Set ppt = Nothing

[mouzi4us]

You appear to be mixing early binding and late binding for powerpoint in the same sub. Why? Usually, only one approach is chosen.



From the perspective of someone taking the time to help you, they are not all that unimportant, since it means a lot of typing and/or guessing about that missing information if the problem is to be replicated.

My guess is that the problem is coming because you are trying to paste to the window instead of to the slide. Here's a potential alternative.


Sub Test()

Set PowerPointApp = CreateObject("PowerPoint.Application")
PowerPointApp.Visible = True
DestinationPPT = "C:\Users\Saeed\Desktop\edit vba\test.pptx"
Set ppPres = PowerPointApp.Presentations.Open(DestinationPPT)
Sheets("Slide3").Range("A2").Copy
With ppPres.Slides(3)
.Shapes.Paste
Set shp = .Shapes(.Shapes.Count)
shp.Left = 17
shp.Top = 90
End With
ppPres.SaveAs "C:\Users\Saeed\Desktop\edit vba" & Filename, ppSaveAsPDF
ppPres.Close
PowerPointApp.Quit
Set ppt = Nothing
Tnx for your response.
You are right about early and late binding. Its somehow a messy job but it works.
I had used the pppres.slide(3).shapes.paste method before and the same error happened. Surprisingly I found out this error is somehow related to memory space (Ram) of my system and whenever I close the background programs it works much better !!
Any way I wanted to know if I can use a "on Error goto label" or not ???
somthing like :

On error GoTo X
X:
Sheets("Slide3").Range("A2").Copy
With ppPres.Slides(3)
.Shapes.Paste
Set shp = .Shapes(.Shapes.Count)
shp.Left = 17
shp.Top = 90
End With

But it seems I dont know exacltly how to use this method so that I can repeat the procedure until I succeed.
It would be appreciated if you could help me use the On Error GoTO .... part

[rlv]

If you are getting an out of memory error then my suspicion turns to the way you are using both early and late binding. Each time you run your code, you are launching two instances of powerpoint (PowerPointApp & ppApp), but at the end of your code, you close only one (ppApp). That seems like repetitive runs might consume memory until at some point you get your error. There is no need to do this, and you should rewrite your code to remove ppApp and rely only on PowerPointApp while remembering to close it at the end


Set PowerPointApp = CreateObject("PowerPoint.Application")
.
.
Your Code Here
.
.
PowerPointApp.Quit


With respect to using On error:
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/on-error-statement