Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Range Link and Chart Link Macros

I recently upgraded to Office 2007 (from Office 2002). I used on a regular
basis macros that allowed me to link either a range or a chart to PowerPoint.
but with this upgrade, neither work. I'd GREATLY appreciate someone's
assistance in explainign what changes are required to the macros below to
allow them to be used in Office 2007.

Many thanks!

mucrick


Sub RangeToPPTLink()
' 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.SlideRa nge.SlideIndex)

' Copy the range as a picture
Selection.Copy

' Paste the range
PPSlide.Shapes.PasteSpecial(Link:=True).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


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.SlideInde x)

' 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



  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Range Link and Chart Link Macros

I should note that the error received is on the following line:

PPSlide.Shapes.PasteSpecial(Link:=True).Select


And the error is as follows:

Runtim error: method 'pastespecial' of object 'shapes' failed




"mucrick" wrote:

I recently upgraded to Office 2007 (from Office 2002). I used on a regular
basis macros that allowed me to link either a range or a chart to PowerPoint.
but with this upgrade, neither work. I'd GREATLY appreciate someone's
assistance in explainign what changes are required to the macros below to
allow them to be used in Office 2007.

Many thanks!

mucrick


Sub RangeToPPTLink()
' 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.SlideRa nge.SlideIndex)

' Copy the range as a picture
Selection.Copy

' Paste the range
PPSlide.Shapes.PasteSpecial(Link:=True).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


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.SlideInde x)

' 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Link Macros Excel 2007 JohnH Excel Discussion (Misc queries) 4 February 14th 08 08:58 PM
How to link an application to Macros? kishore Excel Discussion (Misc queries) 0 December 27th 07 10:14 AM
new link for excel macros [email protected] Links and Linking in Excel 0 October 25th 07 04:22 AM
Hyperlink Link Field to link to file on website BZeyger Excel Programming 0 May 1st 07 05:06 PM
stopping the link of macros in one workbook with every other exce. KyWilde Excel Discussion (Misc queries) 0 March 10th 05 07:35 PM


All times are GMT +1. The time now is 12:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"