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:
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 Office 365. You can find a version of this tip for the older menu interface of Excel here: Changing Huge Numbers of Hyperlinks.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When you copy workbooks that contain links, you may be at a loss as to how to update those links. There are a couple of ...
Discover MoreNeed to create links to many filenames on your system? Here are a couple of quick ways to get just the connection you need.
Discover MoreExcel allows you to define hyperlinks in your worksheets, and these can target specific cells on other worksheets. Here ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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!!!
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments