Batch Search and Replace for Hyperlinks, OLE links, movie links and sound links
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:
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
You copy TEXT.XLSX to
Now you can update the links by running the code above and asking it to replace:
Your link now points to:
And updates when you open your PPT file.
- 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.