0

The code below throws a runtime error on 3 out of 4 runs. I have an large Excel file an I'm trying to copy tables and pictures to a PowerPoint template. The tables are named "WDM_Tab_i" (i for numbers from 1 to 100) and pictures "WDM_Grafik_i_ x"(i same as table and x because some tables have two pictures). I first tried the code with a quite small number of tables and pictures (5 tables and 7 pictures or so). There the code worked perfectly fine. As I started adding tables and pictures, I quite often got runtime errors.

    On Error Resume Next
    For i = 1 To 20
        On Error Resume Next
        holder = Range("WDM_Tab_" & i).Rows.Count
       
        If Err = 1004 Then
            On Error GoTo 0
            Exit For
        Else
            On Error GoTo 0

            Set pptSlide = Pres.Slides.AddSlide(18 + i, pptLayout)
            Pres.Slides(18 + i).Shapes("Titel 1").TextFrame.TextRange.Characters.Text = Range("WDM_Titl_" & i).Value
            
            Range("WDM_Tab_" & i).Copy
            Pres.Slides(18 + i).Shapes.PasteSpecial DataType:=2
            Set myShape = Pres.Slides(18 + i).Shapes(Pres.Slides(18 + i).Shapes.Count)
            myShape.Left = 25
            myShape.Top = 100
            myShape.Width = 450

            x = 1
            For Each sh In Sheets("Weiterdrehmomente").Shapes
                If sh.Name = "WDM_Grafik_" & i & "_" & x Then
                    sh.Copy
                    Pres.Slides(18 + i).Shapes.PasteSpecial DataType:=2
                    Set myShape = Pres.Slides(18 + i).Shapes(Pres.Slides(18 + i).Shapes.Count)
                    myShape.Left = 480
                    myShape.Top = 100 + ((x - 1) * 150)
                    If myShape.Width <= 150 Then
                        myShape.Width = 150
                    ElseIf myShape.Width >= 455 Then
                        myShape.Width = 455
                    End If
                    
                    x = x + 1
                End If
            Next sh
        End If
    Next i

It's This line

Pres.Slides(18 + i).Shapes.PasteSpecial DataType:=2

which gives me the error. When I added "On Error Resume Next" to the whole loop, the output was always different. Sometimes table 5 was missing sometimes picture 7 and in rare occasions, every table and every picture showed in the PP. Can you maybe tell me why it is so inconsistent? Thanks for your help! I am quite confused on how to fix it.

Kuempsmichi
  • 69
  • 2
  • 10
  • You use `On Error Resume Next` wrong. This line just hides the error messages so if you don't see them the errors are still there. If there are errors you didn't fix of course your code cannot work. [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling) – Pᴇʜ May 26 '21 at 09:30
  • 2
    Do you think some operations are taking too much time? would some pauses help processing? or even just the trouble shooting? – Solar Mike May 26 '21 at 09:30
  • Yes i know that "On Error Resume Next" just hides the error. I just used it to see the end result and which data gets transfered to powerpoint. But as i wrote its always different data that works and the error always appears at a different spot and sometimes no error appears. – Kuempsmichi May 26 '21 at 10:15
  • Using the clipboard to copy & paste is inherently flaky, as it is a resource shared amongst all the programmes running on the machine. It could also be that the clipboard is still processing one copy or paste while the code is trying to do another. – DS_London May 26 '21 at 11:54
  • Maybe look at the possibility of saving the table/picture as a file as an intermediate step. – DS_London May 26 '21 at 12:00
  • 1
    `It's This line: Pres.Slides(18 + i).Shapes.PasteSpecial DataType:=2` Also Add one line `Doevents` after `sh.Copy` – Siddharth Rout May 26 '21 at 12:52
  • 1
    This was about Excel but you could take the same approach pasting to PPT: https://stackoverflow.com/questions/67172010/vba-insert-an-image-multiple-times-without-copy-paste/67173303#67173303 – Tim Williams May 26 '21 at 16:03
  • I guess the problem is solved. I first tried `Doevents` solution which seemed to help a bit but there were still errors but less. I then also added a wait function ``Application.Wait Now + TimeSerial(0, 0, 0.6)`` with a delay of 0.6 seconds. 0.5 did still give me errors but 0.6 works perfectly fine. Maybe I have to increase the time when I add more tables and pictures. I will also try the version @Tim mentioned. Maybe that's faster then. Thanks all for your answers. You helped me a lot!! – Kuempsmichi May 27 '21 at 07:12

0 Answers0