PDA

View Full Version : [SOLVED] VBA code Paste Link into PPT



shades
10-22-2004, 10:53 AM
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
10-22-2004, 11:16 AM
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
10-22-2004, 11:47 AM
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
10-22-2004, 12:05 PM
Yep. Just changed it. Thanks for the reminder. You know how us old-himers are.. we forge_ :)

CBrine
10-22-2004, 12:19 PM
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

shades
10-22-2004, 12:41 PM
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
10-22-2004, 04:30 PM
Knowledge Base! Knowledge Base! :D