Make VBA create linked pictures and move to power point

[Rune PS]

Im trying to create a VBA code that will take several ranges from excel, and turn them into linked pictures in a powerpoint presentation, however i cannot get the pictures to become linked. So far, i have managed to combine pieces of code which now makes unlinked pictures and sends those to powerpoint, but i cannot get them to be linked to the excel range.

My current code:

Sub PasteMultipleSlides_testing_link()
Dim myPresentation As Object
Dim mySlide As Object
Dim PowerPointApp As Object
Dim shp As Object
Dim MySlideArray As Variant
Dim MyRangeArray As Variant
Dim x As Long
'Create an Instance of PowerPoint
  On Error Resume Next
    
    'Is PowerPoint already opened?
      Set PowerPointApp = GetObject(class:="PowerPoint.Application")
    
    'Clear the error between errors
      Err.Clear
    'If PowerPoint is not already open then Exit
      If PowerPointApp Is Nothing Then
        MsgBox "PowerPoint Presentation is not open, aborting."
        Exit Sub
      End If
    
    '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
  
'Make PowerPoint Visible and Active
  PowerPointApp.ActiveWindow.Panes(2).Activate
    
'Create a New Presentation
  Set myPresentation = PowerPointApp.ActivePresentation
'List of PPT Slides to Paste to
  MySlideArray = Array(4, 6, 8)
'List of Excel Ranges to Copy from
    MyRangeArray = Array(Sheet1.Range("C4:F11"), Sheet3.Range("C4:F11"), Sheet5.Range("C4:F11"))
'Loop through Array data
  For x = LBound(MySlideArray) To UBound(MySlideArray)
    'Copy Excel Range
        MyRangeArray(x).Copy
    
    'Paste to PowerPoint
        Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2)
    'Position object
        shp.LockAspectRatio = msoFalse
        shp.Left = 133
        shp.Top = 177
        shp.ScaleHeight 0.68, msoFalse, msoScaleFromTopLeft
        shp.ScaleWidth 0.68, msoFalse, msoScaleFromTopLeft
        shp.LockAspectRatio = msoTrue
      
  Next x
'Transfer Complete
  Application.CutCopyMode = False
  ThisWorkbook.Activate
End Sub

Theres some extra lines in there, as the code must also be able to adjust size and position of the ranges, which slides they go on, etc. etc.
If anyone knows how to also make the pictures linked, i would truely appreciate the help

 

[Domenic]

Try...

Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=10, DisplayAsIcon:=msoFalse, Link:=msoTrue) '10 = ppPasteOLEObject

Hope this helps!

 

[Rune PS]

Hello Domenic!

It technically does work and the images are now linked to the excel sheet, which is awsome! however, with this line of code, my current way of adjusting size and positioning (right underneath the line you suggested) no longer works - any suggestions for that?
Appreciate the help!

 

[Domenic]

Actually, Shapes.PasteSpecial returns a ShapeRange object. So the Item method of the ShapeRange object can be used to return a Shape object...

Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=10, DisplayAsIcon:=msoFalse, Link:=msoTrue).Item(1)

 

[Rune PS]

Sorry for the late reply, been busy at work - but this works perfectly! thanks so much