VBA code Paste Link into PPT

[shades]

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

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.

[shades]

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

change these lines:

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

 PPSlide.Shapes.Paste.Select

To this:

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

[Zack Barresse]

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!

[shades]

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

[CBrine]

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

[shades]

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

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

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

[Zack Barresse]

Knowledge Base! Knowledge Base!