Batch Edit Links

[tosti]

Hello,

I have created a powerpoint presentation that links from multiple sheets within one excel workbook.

The issue is I need to update the ppt. based on selecting different excel workbooks (which have the same worksheets). I am able to change the source files individually under the "edit links to file" option.

How can I change the source in powerpoint to schange all the sliLE to another excel workbook at once? Is there a vba code that can do this?

Any advise will be much appreciated.

Thanks

[JohnWilson]

If you post the old and new paths it could be possible

[tosti]

Hi John,

The ppt contains graphs and excel tables.

Original link : C:\MonthEnd\2011Sept.xls

New Path : Original link : C:\MonthEnd\2011Oct.xls

[JohnWilson]

If everything stays the same except the month


Sub fixLinks()
Dim osld As Slide, oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
If oshp.OLEFormat.ProgID Like "*Excel*" Then
oshp.LinkFormat.SourceFullName = Replace(oshp.LinkFormat.SourceFullName, "Sept", "Oct")
End If
End If
Next: Next
End Sub

NOTE the new link location must actually exist or it will crash

[tosti]

Thanks that works but updating all the links however is it possible :
1. For the user to define the path (i.e select the file from a prompt screen as the file link changes on a regular basis).
2. Update the links once the marco is run. (currently i have to manully click on "Update Now" button)

[JohnWilson]

You should be able to work on this I think.

Sub fixLinks()
Dim osld As Slide, oshp As Shape
Dim strpath As String
strpath = InputBox("Enter the new path", "Edit Path", getexisting(ActivePresentation))
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
If oshp.OLEFormat.ProgID Like "*Excel*" Then
oshp.LinkFormat.SourceFullName = strpath
oshp.LinkFormat.Update
End If
End If
Next: Next
End Sub

Function getexisting(opres As Presentation) As String
'this finds the first old link as a prompt
Dim osld As Slide, oshp As Shape
For Each osld In opres.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
If oshp.OLEFormat.ProgID Like "*Excel*" Then
getexisting = oshp.LinkFormat.SourceFullName
Exit Function

If your need are more than this and you cannot edit yourself then as this is obviously a commercial enterprize I'm afraid you will have to hire me!