Changing links to Excel work*sheet* in Powerpoint?

[chuckles1066]

Hi,

This one's driving me crazy; I have a shedload of links to an excel workbook in a Powerpoint presentation; everything works great.

I now need to create an identical presentation which links to the same workbook but to an different worksheet; trying to change the source only allows me to change the workbook, not select the workbook and then drill down to a different worksheet.

Is it possible?

TIA, I am not worthy.

 

[chuckles1066]

Just bumping this as I've found a solution and it might be of interest to others.

This code does the job (thanks and all credit to www.pptfaq.com):

Option Explicit

Sub HyperLinkSearchReplace()

Dim oSl As Slide
Dim oHl As Hyperlink
Dim sSearchFor As String
Dim sReplaceWith As String
Dim oSh As Shape

sSearchFor = InputBox("What text should I search for?", "Search for ...")
If sSearchFor = "" Then
Exit Sub
End If

sReplaceWith = InputBox("What text should I replace" & vbCrLf _
& sSearchFor & vbCrLf _
& "with?", "Replace with ...")
If sReplaceWith = "" Then
Exit Sub
End If

On Error Resume Next

For Each oSl In ActivePresentation.Slides

For Each oHl In oSl.Hyperlinks
oHl.Address = Replace(oHl.Address, sSearchFor, sReplaceWith)
oHl.SubAddress = Replace(oHl.SubAddress, sSearchFor, sReplaceWith)
Next ' hyperlink

' and thanks to several astute user suggestions, let's fix OLE links
' and movie/sound linkes too
For Each oSh In oSl.Shapes
If oSh.Type = msoLinkedOLEObject _
Or oSh.Type = msoMedia Then
oSh.LinkFormat.SourceFullName = _
Replace(oSh.LinkFormat.SourceFullName, _
sSearchFor, sReplaceWith)
End If
Next

Next ' slide

End Sub