Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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.

Changing Huge Numbers of Hyperlinks

Written by Allen Wyatt (last updated March 21, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


6

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 h As Hyperlink
    Dim sOld As String
    Dim sNew As String

    sOld = "c:\" 
    sNew = "S:\Network\"
    For Each h In ActiveSheet.Hyperlinks
        h.Address = Replace(h.Address, sOld, sNew)
    Next h
End Sub

All you need to do is change the values assigned to the sOld and sNew variables.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8624) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Changing Huge Numbers of Hyperlinks.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

AutoText Entries Don't Stick

AutoText can be a great tool, but it is of little worth if you can't get Word to remember the AutoText entries you ...

Discover More

Hiding a Hyperlink on a Printout

Hyperlinks can be real handy in a workbook, but you may not always want them visible when you send the workbook to the ...

Discover More

Using Non-Printing Text Boxes

Text boxes can be helpful for segmenting information from your main document and for creating unique page layouts. What ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Getting Rid of Workbook Links

Excel allows you to easily link information from one workbook to another. If you want to get rid of links that may be ...

Discover More

Tying a Hyperlink to a Specific Cell

Make a hyperlink to a cell in your workbook, edit the structure of that workbook a bit, and you may find that the ...

Discover More

Don't Update Links to Other Programs

If you have links in your workbook to data in other workbooks, you may want to control whether Excel updates those links ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2022-10-15 12:22:39

J. Woolley

For related Tips, see https://excelribbon.tips.net/T011081_Changing_Portions_of_Many_Hyperlinks.html and https://excelribbon.tips.net/T009898_Extracting_Hyperlink_Information.html


2022-10-14 14:27:00

Jill in WA

Hi Allen, Thank you so much for the macro! It worked like a charm.

I had a master file that hyperlinked to a bunch of contracts and related documents on a server. We recently changed servers and I thought I was going to have to manually and individually access the related files. Curiously tried Find and Replace but that didn't handle it. Still curious, I Google searched for options and found your answer. Your macro made my day! All the links are intact again!

Saved your macro to my Personal.xlsb so I can access it easily in the future (hoping I won't need it!). I appreciate your work that made my work easier!


2022-09-20 18:24:19

nouradin nurhussein

one word, brilliant


2021-08-18 13:52:11

Bill Wu

Thank you very much Allen for this handy macro. It has saved me lots of time from manually fixing those hyperlink problems in my excel workbook that were caused by the autosave issue (similar to Emily's).

It works wonderfully!


2020-09-18 05:25:08

Kunlay

Hi There, Greeting from Bhutan,

My Microsoft Excel 2016, hyper link is not functioning as it displays a error message " Your organization's policies are preventing us from completing this action for you. For more info, please contact your help desk".
Please help me sort out his error issue.
Tries every possible way going through options provided in you tube & google searches. but non of the options given could solve the issues i had.
Tried in new excel also but while linking the hyperlink it displays the same error
Please help me out


2020-04-14 12:09:30

Jacob D Davis

LIFESAVER!!!


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.