Batch Search and Replace for Hyperlinks, OLE links, movie links and sound links

Problem

You have a presentation that includes an extensive collection of hyperlinks to various external files.

Then a server name or mapped drive letter changes and all of your links break.

Or you've moved your Excel files from one directory to another and now the links don't work.

You know that all you need to do is change, say, the "P:\" in each link to the new drive name "Q:\" and a simple Search and Replace would do the job. Unfortunately, PowerPoint's Search and Replace feature doesn't work with hyperlink text, so you're stuck doing all of the replacements one at a time by hand.

Or ... you could try this:

Solution

This macro will ask you what text you want to search for and what you want to replace it with.
That it will look at each hyperlink in your presentation and do the search/replace on both the hyperlink Address and Subaddress.

If you're not familiar with hyperlink Address and SubAddress, have a look here:
Hyperlink .SubAddress - How to interpret it

And to get a look at the hyperlinks in the presentation before you start to search and replace, see Show Me: The hyperlinks in my presentation or use the Links Report feature of the free PPTools FixLinks demo.

Here's the code:

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

And another trick ...

At least some versions of PowerPoint permit you to link to files on the internet. It's more convenient to create the links from files on your local PC, though. So you create the links locally, upload the linked files to the web server, then use the macro above to replace the local file path with the server path.

For example, suppose you have links to
C:\MyFiles\Spreadsheets\TEST.XLSX!Sheet1!R1C1:R5C5

You copy TEXT.XLSX to

http://www.yourdomain.xyz/myfiles/spreadsheets/

Now you can update the links by running the code above and asking it to replace:

C:\MyFiles\Spreadsheets\

with

http://www.yourdomain.xyz/myfiles/spreadsheets/

Your link now points to:

http://www.yourdomain.xyz/myfiles/spreadsheets/TEST.XLSX!Sheet1!R1C1:R5C5

And updates when you open your PPT file.

Limitations

• The macro only works in PowerPoint 2000 or greater. It won't run in PowerPoint 97 (because of the Replace command, which isn't supported in 97).
• The macro only works on hyperlinks and OLE links, not action settings. But some action settings (most of the Link To ones) are hyperlinks as well, so links to other files, whether hyperlinks or action settings, will generally work with this macro.

See How do I use VBA code in PowerPoint? to learn how to use this example code.

---