GoTo Error Handling Not Working

[MissCue14]

I am trying to copy a table from Excel and paste as an image to PowerPoint.

For some reason, the copy/paste methods error out about 1 in 10 attempts to run the code.

• I added GoTo error handling but the code seems to be ignoring it.
  • On the line "Graphic.Range("A1:AC102").CopyPicture" I receive 1004 errors.
  • On the line "mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile" I receive -2147188160 (80048240) errors.
• I also tried adding a wait time to see if the file speed was causing the error, to no avail.
• I have searched the net and all the forums and cannot find the resolution to my issue.

Any help would be greatly appreciated.

Here is the section of the code that is causing the errors (it's quite a long script in total):

'Set PowerPoint Variables
    Set PPTApp = CreateObject("PowerPoint.Application")
    Set PPTPres = PPTApp.Presentations.Open(PPTTempLoc & PPTTemp)
    PPTApp.Visible = True
    Set mySlide = PPTPres.Slides(1)
    
    'Slow down the code to prevent errors in copy method
    Application.Wait (Now + TimeValue("0:00:01"))
        
    'Copy Table from Excel
    On Error GoTo 999 'handles errors in copy/paste method
    Graphic.Range("A1:AC102").CopyPicture

    'Slow down the code to prevent errors in paste method
    Application.Wait (Now + TimeValue("0:00:01"))
    
    'Paste Table in PowerPoint
    mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
    Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
    
    'Reset error handling
    On Error GoTo 0

Here is the error handling:

999:
    'If error, repeat copy and resume
    If Err.Number <> 0 Then
        Graphic.Range("A1:AC102").CopyPicture
        On Error Resume Next
    End If

I am using Excel 2013 and Windows 10.

I can provide the full code if needed, but I cannot provide the data as it is proprietary.

 

[bobsan42]

Try to make sure that Excel is the active application when you copy - use a worksheet object to make sure you actually copy an excel range rather than something from Powerpoint.
And maybe submit the complete error messages and pay close attention to the title.
Also I can't really explain why but something tells me it is not a good idea to use line numbers - I would use a label - something like errHandler:

 

[MissCue14]

Huzzah! You are a lifesaver! It was Excel not being the active window that was causing the error. I can't believe I missed that. Adding to my list of things to troubleshoot for future reference. Thank you so much!

 

[Steve_]

Huzzah! You are a lifesaver! It was Excel not being the active window that was causing the error. I can't believe I missed that. Adding to my list of things to troubleshoot for future reference. Thank you so much!

My recommendation is to get in a habit of fully qualifying things.

Instead of using Cells(x,y) for example, use Thisworkbook.Sheets("SheetName").cells(x,y)

This eliminates the need to manage your active window.

 

[MissCue14]

Sigh. And now I receive an error on the Paste.

Here is the updated code:

    'Set PowerPoint Variables
    Set PPTApp = CreateObject("PowerPoint.Application")
    Set PPTPres = PPTApp.Presentations.Open(PPTTempLoc & PPTTemp)
    PPTApp.Visible = True
    Set mySlide = PPTPres.Slides(1)
    
    'Copy Table from Excel
    Main.Activate 'Make Excel active window
    On Error GoTo errHandler 'handles errors in copy/paste method
    Graphic.Range("A1:AC102").CopyPicture

    'Paste Table in PowerPoint
    PPTApp.Activate
    mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
    Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
    
    'Reset error handling
    On Error GoTo 0

And the error is "Run-time error '-2147188160 (80048240)': Shapes.PasteSpecial : Invalid Request. The specified data type is unavailable."

 

[bobsan42]

This error should show up upon Compiling - good practice before running your code. Which you probably have not done
It is not a good idea to use Enum names instead of values when working with different applications than the one running the code.
Try like this:

mySlide.Shapes.PasteSpecial 2

 

[MissCue14]

I thought I had fully qualified everything, did you see something I missed? I am self taught so there may be nuances I am not aware of. For example, Graphic.Range("A1:AC102").CopyPicture should be fully qualified because I have "Graphic" defined as a worksheet in the variables. Maybe this is not correct?

 

[MissCue14]

This error should show up upon Compiling - good practice before running your code. Which you probably have not done
It is not a good idea to use Enum names instead of values when working with different applications than the one running the code.
Try like this:

mySlide.Shapes.PasteSpecial 2

I updated the code with your suggestion and it does not help. Ultimately, the file loops through a list of suppliers and prints information to individual PowerPoints which are saved on a drive. There are 45 suppliers on list. It runs fine for about 1/2 to 3/4 of the instances then throws the error. I do not touch the mouse or keyboard during that time. If I go into Debug mode then F5 it continues along without having to touch anything else. Additionally, it has started giving my 1004 errors again on the copy line.

Here is the current code (include the qualifications I do earlier on):

    Dim Main As Workbook
    Dim Graphic As Worksheet
    Dim PPTApp As PowerPoint.Application
    Dim PPTPres As PowerPoint.Presentation
    Dim mySlide As Object
    Dim myShape As Object

    Set Main = ThisWorkbook
    Set Graphic = Main.Sheets("Graphic")

.........

    'Set PowerPoint Variables
    Set PPTApp = CreateObject("PowerPoint.Application")
    Set PPTPres = PPTApp.Presentations.Open(PPTTempLoc & PPTTemp)
    PPTApp.Visible = True
    Set mySlide = PPTPres.Slides(1)
    
    'Copy Table from Excel
    Main.Activate 'Make Excel active window
    On Error GoTo errHandler 'handles errors in copy/paste method
    Graphic.Range("A1:AC102").CopyPicture

    'Paste Table in PowerPoint
    PPTApp.Activate
    mySlide.Shapes.PasteSpecial 2
    Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
    
    'Reset error handling
    On Error GoTo 0

Can you please clarify when you say "Enum names", I tried Googling, but I would like to know your context. Apologies if I am slow to understand, I do not have formal training in VB so I do now know the proper vocabulary to use when discussing code. I will also need to look in Compiling.

Thank you!

 

[bobsan42]

I am also not sure my VBA terms are 100% correct.
Enumerated type is when you represent a value as a text string - Usually function parameters like ppPasteEnhancedMetafile actually equals 2.
basically the function takes a number parameter but for people is easier to understand the text.

And I think something takes some time to happen so you get an error at the copy line - try inserting DoEvents and do not unnecessarily use Activate , and maybe you have to alter the variable declarations slightly.
You declare pptapp as application but then you use createobject.
Try the code like this:

    Dim Main As Workbook
    Dim Graphic As Worksheet
    Dim PPTApp As Object 
    Dim PPTPres As Object 
    Dim mySlide As Object
    Dim myShape As Object


    Set Main = ThisWorkbook
    Set Graphic = Main.Sheets("Graphic")


.........


    'Set PowerPoint Variables
    Set PPTApp = CreateObject("PowerPoint.Application")
    Set PPTPres = PPTApp.Presentations.Open(PPTTempLoc & PPTTemp)
    Set mySlide = PPTPres.Slides(1)
    
    'Copy Table from Excel
    On Error GoTo errHandler 'handles errors in copy/paste method
    DoEvents
    Graphic.Range("A1:AC102").CopyPicture


    'Paste Table in PowerPoint
    mySlide.Shapes.PasteSpecial 2
    Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
    PPTApp.Visible = True



    'Reset error handling
    On Error GoTo 0

 

[MissCue14]

So that helps, but does not solve it. I added an wait time of 1 sec as well. Between the two, the error only occurs every 2-3 times running the file. I had added the Activate earlier per Steve_'s suggestion, but I have removed it. If I understand correctly, the "Set Graphic = Main.Sheets("Graphic")" line takes care of the need for setting the active window. Thank you for your time!