VBA code Paste Link into PPT

I have been using this code for several months, and it works perfectly.

Code:

---

Sub RangeToPresentation()
' Set a VBE reference to Microsoft PowerPoint 10.0 Object Library for Office 2002,
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
        MsgBox "Please select a worksheet range and try again.", vbExclamation, _
                "No Range Selected"
Else
        Set PPApp = GetObject(, "Powerpoint.Application")
        ' Reference active presentation
        Set PPPres = PPApp.ActivePresentation
        PPApp.ActiveWindow.ViewType = ppViewSlide
        ' Reference active slide
        Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
 
        ' Copy the range as a picture
        Selection.CopyPicture Appearance:=xlScreen, _
                Format:=xlPicture
        ' Paste the range
        PPSlide.Shapes.Paste.Select
 
        ' Align the pasted range
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
        ' Clean up
        Set PPSlide = Nothing
        Set PPPres = Nothing
        Set PPApp = Nothing
End If
End Sub

---

However, now I would like the paste in PowerPoint to be PasteSpecial. Link, so that it will be a live link. Is there something that can be changed easily to accomplish this? I have never used VBA in PPT.

Any help would be appreciated.

Okay, a little experimenting, and I have found the answer:

change these lines:

Code:

---

Selection.CopyPicture Appearance:=xlScreen, _
                Format:=xlPicture

 PPSlide.Shapes.Paste.Select

---

To this:

Code:

---

        Selection.Copy
        ' Paste the range
        PPSlide.Shapes.PasteSpecial(Link:=True).Select

---

Quote:

---

Originally Posted by shades

Okay, a little experimenting, and I have found the answer: ...

---

Solved then? D'ya wanna mark it as such? :) And thanks for posting that solution Shades. Very helpful!

Yep. Just changed it. Thanks for the reminder. You know how us old-himers are.. we forge_ :)

Hey Shades,

Thanks for that from me as well. About a month ago I was trying to figure out how to link an excel graph to PPT presentation, and all I was able to come up with was using the insert object, with a link to an excel object. The only problem was you couldn't control what was shown in the box on the PPT. After reading you code and testing the paste link, it work perfectly. No more copying, resizing, aligning,copying, resizing, aligning,etc... for about 12 graphs:bug: :yay :yay :yay

Yep, that is my intention: Here is the code for the chart link:

--------------------

Code:

---

Sub ChartToPPTLink()
' Set a VBE reference to Microsoft PowerPoint 10.0 Object Library for Office 2002,
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
' Make sure a chart is selected
If ActiveChart Is Nothing Then
        MsgBox "Please select a chart and try again.", vbExclamation, _
                "No Chart Selected"
Else
        Set PPApp = GetObject(, "Powerpoint.Application")
        ' Reference active presentation
        Set PPPres = PPApp.ActivePresentation
        PPApp.ActiveWindow.ViewType = ppViewSlide
        ' Reference active slide
        Set PPSlide = PPPres.Slides _
                (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
       
        ' Copy chart as a picture
        ActiveChart.ChartArea.Copy
        ' Paste chart link
        PPSlide.Shapes.PasteSpecial(Link:=True).Select
        ' Clean up
        Set PPSlide = Nothing
        Set PPPres = Nothing
        Set PPApp = Nothing
End If
End Sub

---

Knowledge Base! Knowledge Base! :D