Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Changing Huge Numbers of Hyperlinks.
by Allen Wyatt
(last updated March 15, 2014)
Wendy has a single Excel worksheet that contains over 1,200 hyperlinks to TIFF files. (These are hyperlinks, not regular links.) Excel hiccupped and had to shut down, so Emily used the AutoSaved files to recover the previously saved file. Now all the previously working hyperlinks don't work. She had the hyperlinks to the images on a shared network drive, but the AutoSave changed the hyperlinks to reference the C: drive. She wonders if there is an easy way to fix them back to the shared network drive.
At first blush it might seem that you could use Excel's regular Find and Replace feature to find the hard drive designation (as in file://c:) and replace it with a network drive (as in file://shareddrive). The problem is that this approach only addresses part of the problem—it only changes the displayed portion of the hyperlink, not the underlying hyperlink itself. The only way you can get to the hyperlink itself is through the use of a macro.
Assuming that all the hyperlinks that need changing are on the same worksheet, then you can use the following macro:
Sub FixHyperlinks() Dim wks As Worksheet Dim hl As Hyperlink Dim sOld As String Dim sNew As String Set wks = ActiveSheet sOld = "c:\" sNew = "S:\Network\" For Each hl In wks.Hyperlinks hl.Address = Replace(hl.Address, sOld, sNew) Next hl End Sub
All you need to do is change the values assigned to the sOld and sNew variables.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8624) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Changing Huge Numbers of Hyperlinks.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you need to modify the URL used in a large number of hyperlinks, you can do so by using a macro and a little ...Discover More
If you use UNC paths in your links to external information, those paths may need to be changed at some point. You can ...Discover More
Combining workbooks that have cross-links to each other can offer some special challenges. This tip examines how you can ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.