VBA pasting from Excel to PowerPoint has stopped working

Question

The attached code was working until this week. Now I get a "Shapes (unknown member) : Invalid request error when the PasteSpecial line is reached. I've checked and the chart I want to paste is on the clipboard, and I can paste it directly into the slide from the clipboard. This is code from an Access database that is building a PowerPoint presentation using charts from an Excel spreadsheet. Using Microsoft® Access® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 64-bit.

Set wb1Sheet = wb1WB.Sheets(1)
wb1Sheet.Activate
Set wb1ChartObj = wb1Sheet.ChartObjects(t)
wb1ChartObj.Activate
Set wbChart = wb1ChartObj.Chart
wbChart.ChartArea.Copy
activeSlide.Shapes.PasteSpecial (ppPasteMetafilePicture)
activeSlide.Shapes(1).TextFrame.TextRange.Text = nAme7
activeSlide.Shapes(4).Left = 12 
activeSlide.Shapes(4).Top = 194 
activeSlide.Shapes(4).Width = 339

I've searched for any answers and can find no reason why it is no longer working. I've gone to saved older versions that worked and they no longer work either.

Answer 1

I'm guessing that it might be a timing issue. Try pausing the macro for a few seconds after you copy, and before you paste. So, for example, first add the following procedure to your module...

Sub PauseMacro1(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
    
    Do
        DoEvents
    Loop Until Timer > endTime
    
End Sub

Then try...

'
'
'

wbChart.ChartArea.Copy

PauseMacro 3 'seconds

activeSlide.shapes.PasteSpecial ppPasteMetafilePicture

'
'
'

Answer 2

I have a similar code that worked fine for years... and stopped working this week too! (although I haven't edited it)

For some reason, it looks like ppPasteMetafilePicture now creates an error: changing it to ppPastePNG worked for me!

FYI, I am working with Office Professional Plus 2021. Hope this helps...

Answer 3

It looks to me like they have updated the names of how to paste. I changed mine to ppPasteEnhancedMetafile and that seems to have fixed the bug.

I think for longer term solutions, incase they change the name in another 10 years, then the number works as well. PasteSpecial(2)