Make VBA create linked pictures and move to power point

Rune PS

New Member
Joined
Oct 9, 2018
Messages
8
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:

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 :)
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try...

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

Hope this helps!
 
Upvote 0
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!
 
Upvote 0
Actually, Shapes.PasteSpecial returns a ShapeRange object. So the Item method of the ShapeRange object can be used to return a Shape object...

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

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top