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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If you have linked information in your worksheets, you may want a way you can easily change the targets to which those ...Discover More
Make a reference to a hyperlink in a formula, and you may be surprised that the reference doesn't return an active ...Discover More
Combining workbooks that have cross-links to each other can offer some special challenges. This tip examines how you can ...Discover More
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.