This is part of my code, which pastes data from Excel to PowerPoint. Usually it works, but every once in a while (maybe after 10 or 20 runthroughs, each with about 15 PasteSpecials of the same type), I get an error: "Shapes (unknown member): Invalid request. The specified data type is unavailable." The debugger points to the line mySlide.Shapes.PasteSpecial DataType:=2. Can someone figure out why?

If you were to ask me why I'm using this data type, I can only say because it worked. I tried around and this one seemed to accurately copy what I saw in Excel to PowerPoint (others 'forgot' borders and the like).

Sub MReport()

    Dim rng As Range, rngAn As Range
    Dim myShape As PowerPoint.Shape
    Dim DestinationPPT As String
    Dim lRow As Long, lCol As Long
    Dim wbM As Workbook
    Dim wsEm As Worksheet
    Dim CSGSheet As Variant, CSGSheets As Variant
    Dim MonthNum As String, YearNum As String

    Set PowerPointApp = New PowerPoint.Application
    DestinationPPT = "C:\VBA\ReportTemplate.pptm"
    Set myPresentation = PowerPointApp.Presentations.Open(DestinationPPT)

    YearNum = "2019"
    MonthNum = "02"

    Set wbM = Workbooks.Open("C:\VBA\" & YearNum & "\" & Right(YearNum, 2) & "_" & MonthNum & "\NumbersM_" & YearNum & "_" & MonthNum & ".xlsm", UpdateLinks:=False)

    CSGSheets = Array("CSG_BM", "CSG_AR", "CSG_ISF")

    For Each CSGSheet In CSGSheets
        lRow = wbM.Sheets(CSGSheet).Cells(Rows.Count, "B").End(xlUp).End(xlUp).End(xlUp).Row + 1
        lCol = wbM.Sheets(CSGSheet).Cells(lRow - 1, "B").End(xlToRight).Column + 1
        Set rng = wbM.Sheets(CSGSheet).Range(wbM.Sheets(CSGSheet).Cells(2, 2), wbM.Sheets(CSGSheet).Cells(lRow, lCol))
        rng.Copy
        Set mySlide = myPresentation.Slides.Add(myPresentation.Slides.Count + 1, 12)
        PowerPointApp.ActiveWindow.ViewType = ppViewNormal
        mySlide.Shapes.PasteSpecial DataType:=2  '2 = ppPasteEnhancedMetafile
        Set myShape = mySlide.Shapes(mySlide.Shapes.Count)

        With myShape
          .Height = 410
          .Top = 70
          .Left = 5
        End With
    Next

    wbM.Close SaveChanges:=False
End Sub

0 Answers