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