1

In my Macro which is meant to export certain ranges/tables from Excel to Powerpoint, I keep randomly getting the error:

run-time error '-2147188160 (80048240)': Shapes.pastespecial : Invalid request. The specified data type is unavailable.

Randomly meaning the error might occur at the first range, the next time it pastes all ranges without problem, then at the 3rd range etc. I have looked at multiple posts and different solutions. Nothing worked. This included adding:

DoEvents
PPTApp.ActiveWindow.ViewType = 9
PPTApp.ActiveWindow.Panes(2).Activate
Application.Wait (Now + TimeValue("0:00:01"))

Here is the code:

'Powerpoint Variables
Dim PPTApp As Object
Dim myPresentation As Object
Dim DestinationPPT As String
Dim myShape As Object
Dim mySlide As Object

'Excel Variables
Dim rng As Range

'Create an Instance of PowerPoint
On Error Resume Next
Set PPTApp = CreateObject("Powerpoint.application")
DestinationPPT = Sheets("Input").Range("I21").Value 'Powerpoint File specified in Excel Input Tab
PPTApp.Presentations.Open (DestinationPPT)

'Handle if the PowerPoint Application is not found
If Err.Number = 429 Then
    MsgBox "Powerpoint could not be found.aborting."
    Exit Sub
End If
On Error GoTo 0

'Set my current Powerpoint window as activated
Application.ScreenUpdating = False
Set myPresentation = PPTApp.ActivePresentation


'--- SPECIFY & COPY RANGES START ---

'Slide 2 - KPI
Set rng = Worksheets("2_KPI").Range("A1:M24") 'Specify Range
rng.Copy
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
Set mySlide = myPresentation.Slides(2) 'Set slide number
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
myShape.Left = 248
myShape.Top = 50

'Slide 2 - Auslastung Monat
Set rng = Worksheets("2_KPI").Range("S26:T27") 'Specify Range
rng.Copy
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
Set mySlide = myPresentation.Slides(2) 'Set slide number
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
myShape.Left = 550
myShape.Top = 85

'Slide 2 - Patienten Monat
Set rng = Worksheets("2_KPI").Range("U28:V29") 'Specify Range
rng.Copy
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
Set mySlide = myPresentation.Slides(2) 'Set slide number
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
myShape.Left = 618
myShape.Top = 85

'Slide 3 - Tabelle unten links
Set rng = Worksheets("2_KPI").Range("O29:Q36") 'Specify Range
rng.Copy
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
Set mySlide = myPresentation.Slides(3) 'Set slide number
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
myShape.Left = 28
myShape.Top = 268

'--- SPECIFY & COPY RANGES END ---
Application.CutCopyMode = False 'Clear The Clipboard
'Make PowerPoint Visible and Active
PPTApp.Visible = True
PPTApp.Activate

Any ideas are appreciated.

David
  • 11
  • 1
  • Does increasing the amount of wait time help? Unpredictable VBA paste errors are usually due to timing issues. – John Korchok Feb 04 '21 at 17:39
  • Sometimes multiple DoEvents are necessary. Also see Jon Peltier's post here, where he suggests moving at least the Paste part and possibly other bits into separate subroutines. https://stackoverflow.com/questions/50133777/error-when-copy-pasting-a-chart-through-vba – Steve Rindsberg Feb 04 '21 at 21:17

0 Answers0