My VBA code does not recognize the shape it pasted in excel. However, if I run the code after not changing anything in debug it suddenly works

Question

So, I am writing this code that is supposed to loop through several tables in Excel and paste it into PowerPoint. This process works fine. But, after pasting the table as a range I am trying to change the dimensions of the shape (#7 in the case of my slides). However, at this point the code Set PPShape = PPSlide.Shapes(7) and gives me the following error "Method 'Item' of object 'Shapes' failed (see image)

error given

So, it does not recognize the shape that it just copied into PowerPoint as a shape.

However, once I click debug and run the code again without changing a single line of code it works. VBA then does detect shape #7 on the respective slide and changes its dimensions to the given parameters. I have tried to write the process of selecting the shape and resizing it in a different subroutine, and then call this subroutine from within the original one. No luck. And, On Error also does not seem to get past this bug. Anybody got an idea of how to fix this?

Option Explicit
Sub DBible()

'Declaring all necessary PowerPoint variables
Dim PP As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PPShape As PowerPoint.Shape
Dim OriginFile As String 'File path to PowerPoint template

'Declaring all necessary Excek-l variables
Dim adminSh As Worksheet 'Sheet containing the data to be exported
Dim configRng As Range 'Selection of cells used to mark the exported ranges
Dim rng As Range 'Each individual cell within range
Dim vRange$ 'Cell address
Dim expRng As Range 'Range to be exported

'Dimension variables to resize and position exported shapes
Dim vWidth As Double
Dim vHeight As Double
Dim vTop As Double
Dim vLeft As Double
Dim vSlide_No As Double 'Slide in which range will be pasted

'Create and open PowerPoint
Set PP = CreateObject("PowerPoint.Application")

OriginFile = "https://'CompanyName-my.sharepoint.com/personal/CompanyName/Documents/Documents/PLS%20DO%20NOT%20TOUCH%20-%20BRP%20ORIGIN.pptx?web=1"
PP.Presentations.Open (OriginFile)
PP.Visible = True
PP.WindowState = ppWindowMaximized

Set PPPres = PP.ActivePresentation

'Select sheet where data will be exported from and define the origin of all the ranges
Set adminSh = Sheets("PERF-LIQ-VEL-BETA")
Set configRng = adminSh.Range("rng_sheets")

'Loop that starts process of copy pasting the ranges from Excel to PowerPoint
For Each rng In configRng

    With adminSh 'Defining value of variables or location in which value of variable can be found in the excel
        vRange$ = .Cells(rng.Row, 6).Address
        vTop = 127
        vLeft = 14.2
        vWidth = 692.64
        vHeight = 235
        vSlide_No = .Cells(rng.Row, 4).Value
    End With
    
    PPPres.Windows(1).Activate
    PPPres.Windows(1).View.GotoSlide (vSlide_No)
    
    Set expRng = Sheets("PERF-LIQ-VEL-BETA").Range(vRange$).CurrentRegion 'Set range from excel
    
    expRng.Copy 'Set range from excel
    PP.CommandBars.ExecuteMso "PasteSourceFormatting" 'Paste
    
    Set PPSlide = PPPres.Slides(vSlide_No)
    Set PPShape = PPSlide.Shapes(7) 'Set shape to be altered
    
    With PPShape 'Resize and position range
        .Top = vTop
        .Left = vLeft
        .Width = vWidth
        .Height = vHeight
    End With
    
    'Clear memory to avoid errors in process of copying next range
    Set PPShape = Nothing
    Set PPSlide = Nothing
    Application.CutCopyMode = False
    
Next rng 'Repeat
    
End Sub

"On Error also does not seem to get past this bug" - On Error will never "get past" any bug! Could it be a timing issue - Excel is still in the act of pasting the shape into PowerPoint perhaps? Try inserting a `DoEvents` after the `ExecuteMso` - it's not best practice but in this case might give us a clue that the problem is timing. Alternatively, put a breakpoint on the line in question, run it to that point, pause briefly before hitting F5 again - will prove timing or refute it — CHill60, Jul 27 '23 at 10:08
This same problem has come up in one form or another quite a few times; do a bit of searching here. Start with https://stackoverflow.com/questions/23513139/how-long-does-it-take-the-vba-copy-method-to-populate-the-clipboard-in-powerpoi — Steve Rindsberg, Jul 27 '23 at 13:58

Oscar Sun · Answer 1 · 2023-07-28T11:21:24.553

Try to change these:

1. PP.CommandBars.ExecuteMso "PasteSourceFormatting" 'Paste

to

PPSlide.Shapes.Paste

2. Set PPShape = PPSlide.Shapes(7)

to

Set PPShape = PPSlide.Shapes(PPSlide.Shapes.count)

so like this:

...
        expRng.Copy 'Set range from excel
        'PP.CommandBars.ExecuteMso "PasteSourceFormatting" 'Paste
        
        Set PPSlide = PPPres.Slides(vSlide_No)
        'Set PPShape = PPSlide.Shapes(7) 'Set shape to be altered
        PPSlide.Shapes.Paste
        
        Set PPShape = PPSlide.Shapes(PPSlide.Shapes.Count)
        
        With PPShape 'Resize and position range
            .Top = vTop
            .Left = vLeft
            .Width = vWidth
            .Height = vHeight
        End With
...

You can also use the return ShapeRange object of the PPSlide.Shapes.Paste method to get the PPShape, like this:

...
        expRng.Copy 'Set range from excel
        'PP.CommandBars.ExecuteMso "PasteSourceFormatting" 'Paste
        
        Set PPSlide = PPPres.Slides(vSlide_No)
        'Set PPShape = PPSlide.Shapes(7) 'Set shape to be altered
        
        Dim sr As PowerPoint.ShapeRange
        
        DoEvents
        Set sr = PPSlide.Shapes.Paste
        
        'Set PPShape = PPSlide.Shapes(PPSlide.Shapes.Count)
        Set PPShape = sr.Item(sr.Count)
        
        With PPShape 'Resize and position range
            .Top = vTop
            .Left = vLeft
            .Width = vWidth
            .Height = vHeight
        End With
...

Oran G. Utan · Answer 2 · 2023-08-07T09:49:28.877

Your computer is very likely too fast, and it processes the pasting while the copying is still not completed (there are plenty of questions about this behaviour). The solution normally proposed is to delay the process, which can work, but what if the computers get even faster? More delays? And for how long should the delay last? The hint I found most interesting and more elegant, making it also applicable at class level I think, is to create separated procedures that handle that, so we should be certain that, before exiting, both copy and paste have been processed. Below with a sample usage. The copy, as you can see, can be directly used both in PowerPoint and Excel (as it searches for the parent, be it the worksheet or the slide, maybe Word, too? I am not practical so I cannot say), while for paste in Excel you would need to use the one below with the Worksheet (I have not tested it yet, but I guess it should work). Else, they could just be Objects to cut it short and take all possibilities.

Sub caLLcopyPaste()
Dim sL As Slide: Set sL = ActivePresentation.Slides(1)
Dim sLTarget As Slide: Set sLTarget = ActivePresentation.Slides(2)
Dim sH As ShapeRange: Set sH = sL.Shapes.Range("Group 1")
Call copyShape(sH)
Call pasteShape(sLTarget)
End Sub


Sub copyShape(sH As ShapeRange)
sH.Parent.Shapes.Range(sH.Name).Copy
End Sub


Function pasteShape(sLTarget As Slide) As ShapeRange
Set pasteShape = sLTarget.Shapes.Paste
End Function

'Function pasteShapeWS(sLTarget As Worksheet) As ShapeRange
'Set pasteShape = sLTarget.Shapes.Paste
'End Function

Update

Pasting to Excel was a bit trickier,

the copying works the same

Sub copyShape(sH As ShapeRange)
sH.Parent.Shapes.Range(sH.Name).Copy
End Sub

to paste in Excel from PowerPoint though this works (how to call):

 pasteShapeExcL wSTarget

the actual pasting

Sub pasteShapeExcL(wrKs As Excel.Worksheet)
wrKs.Paste
End Sub

However, copying from Excel to PowerPoint is different since I am now having difficulties:

Dim sH0XcL As Excel.ShapeRange: Set sH0XcL = wSTarget.Shapes.Range(Array("Rectangle 1"))

this is the only way I have found so far to avoid mismatch error at the setting, which in turns reflects on the copy sub... so the processes are slightly different and need more tweaking.

My VBA code does not recognize the shape it pasted in excel. However, if I run the code after not changing anything in debug it suddenly works

2 Answers2

Linked