Batch Update Links on Link Table

[shasta30]

Hi everyone,

New to this board.

I have a PowerPoint template linked to an Excel template. I have about 20 linkeds charts, named ranges, etc. in my PowerPoint that links to the Excel template.

Users open each template, rename for their particular project, and then save to a new network location (different every time). I can update the links in PowerPoint each individually, but this is too cumbersome for beginners. I need to automate the process.

#1: Is there a way to either batch process the update link command by pasting a URL (this will be an intranet location using Sharepoint) -- or --
#2: Add a "Browse File Location" command to this VBA script I borrowed from another post? (BTW: this script runs through all, then fails. Not sure why?)


(This Macro will run in PowerPoint, but I figured Excel Power Users know what to do )
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
End If
End If
Next: Next
End Function

I know just enough to be dangerous!

Any help or alternate solutions are most appreciated!

Thank you,

shata30

 

[shasta30]

Hello all,

I found a solution here:

Change Source for Links to Excel charts from PowerPoint

Works exactly as I need it to.

Thank you everyone!