Paste Links

[The_Horse]

I've searched the Web without luck on this one...:banghead:

I have a mega spreadsheet (Excel 2007) with numerous tables and graphs - which I want pasted into a Powerpoint 2007 report. I normally use the Paste Special Enhanced Metafile option (quality graphics - no dynamic update needed)...but this time round I required the ability to perform dynamic updates between Excel and Powerpoint.

All is fine - except when it comes to changing the source file name in Excel (versions / different directories etc.) - there appears to be no mass-update method of changing the source filename for Paste Links in Powerpoint - other than the incredibly slow approach of one link at a time.

Can't find a solution out there - does anyone know a VBA approach to this, or have I (incredibly !!) stumbled upon a weakness in Powerpoint that nobody else has hit ?

Thanks

Mick

PS - the main reason for changing the Excel source was to step down the source file from 2007 to Excel 2003...er, knocking off the "x" at the end of the "xlsx" suffix !

[John Wilson]

I would have thought something like this would work:
Sub change_link()
Dim osld As Slide
Dim oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
oshp.LinkFormat.SourceFullName = Replace(oshp.LinkFormat.SourceFullName, "xlsx", "xls")
End If
Next oshp
Next osld
End Sub

[The_Horse]

John,

superb - worked first time !
Definitely a solution to promulgate - thanks

Mick

[John Wilson]

That's great.