Copy embedded tables from Excel to PowerPoint with VBA

[_Gregor]

Hey all,

On one of the projects I have an excel file and a macro to automaticaly generate 20 PowerPoints with tables imbeded so that they can be updated when the excel numbers change.
To do this I call the following Sub:

Sub pp_Paste(wb As String, ws As String, Ran As String, nSlide As Integer, pp As Variant, posWidth As Integer, posTop As Integer, posLeft As Integer)
   Dim Object1 As Variant
   Set Object1 = Workbooks(wb).Worksheets(ws).Range(Ran)
   Object1.Copy
   pp.Slides(nSlide).Select
   
   With pp.Slides(nSlide).Shapes.PasteSpecial(DataType:=ppPasteOLEObject, _
               Link:=msoTrue)
       .Left = posLeft
       .Width = posWidth
       .Top = posTop
   End With
   
   'clean
   Object1 = Empty
   Application.CutCopyMode = False
End Sub

The problem is that it offten crashes with an error "Shapes.PasteSpecial: Invalid request. Specified data type is unavalible". If after that I go to the debug mode, and run the code again, the macro runs perfectly. My hypothesis is that that excel tries to paste before the copy comand was finished, but I am not sure.

So my question is either if there is a way to implement this differently or if it can be fixed.

Thanks for your help

 

[Worf]

Hi and welcome to the Board

This is working for me:

Sub pp_Paste(wb$, ws$, Ran$, nSlide%, pp, posWidth%, posTop%, posLeft%)
Dim Object1, s
Set Object1 = Workbooks(wb).Worksheets(ws).Range(Ran)
Object1.Copy
Application.Wait TimeValue("0:00:02") + Now()
pp.Slides(nSlide).Select
DoEvents
pp.Slides(nSlide).Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoTrue
Set s = pp.Slides(nSlide).Shapes(pp.Slides(nSlide).Shapes.Count)
s.Left = posLeft
s.Width = posWidth
s.Top = posTop
Object1 = Empty
Application.CutCopyMode = False
End Sub


Sub Main()
Dim pp As PowerPoint.Presentation, papp As PowerPoint.Application
Set papp = GetObject(, "PowerPoint.Application")
Set pp = papp.ActivePresentation
pp_Paste "Automation", "Master", "b2:h9", 2, pp, 200, 90, 40
End Sub