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.

Changing Huge Numbers of Hyperlinks

by Allen Wyatt
(last updated March 15, 2014)

95

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.

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

Automatic Row Height For Merged Cells with Text Wrap

When you have text wrap turned on in a cell, Excel expands the height of the row as you add more text to the cell. When you ...

Discover More

Superscript and Subscript at the Same Place

Do you want a superscript and subscript character to appear directly above each other? There are multiple ways you can ...

Discover More

Highlighting Every Thousandth Character

Not satisfied with the detail provided by the Word Count feature in Word? Perhaps you want to actually know where every one ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (RIBBON)

Removing Hyperlinks without a Macro, Take Two

Need to get rid of hyperlinks in a worksheet? Here's an easy way to do it without using a macro.

Discover More

Changing Link References

If you use UNC paths in your links to external information, those paths may need to be changed at some point. You can easily ...

Discover More

Changing Portions of Many Hyperlinks

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 ingenuity. ...

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 for this tip:

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. 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 seven minus 6?

2017-03-09 05:19:43

Murat35

@ErwinR55

Thanks a lot. The macro was not working at all. After running your macro first, I saw that the actual links are different than the ones it shows when you "mouse over".

It was ..\..\..\folder\file.pdf

In the main macro, I have changed the values;

sOld = "..\..\..\"
sNew = "c:\users\username\desktop\"

Worked like charm! Thanks a lot!

Now I'll add an InputBox for the other users to whom I will handover this file and the folders so that they can change the locations of the folders as they wish and refresh the links accordingly.


2017-03-02 11:40:15

ErwinR55

Pfff, finally found out why it's not working

Had to use the next code to make it clear what was wrong (this macro shows the actual hyperlink in the next colom, so try it out in test version of your file)

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

This will tell you if your "mouse over" shown hyperlink is the same as the actual hyperlink that is in your file that you are trying to change

What was the case: My hyperlink when "mouse over" shows as: file:///\\NETWORK_NAME\MAP_NAME_WHERE_EXCELFILE_IS_LOCATED\FINAL_DESTINATION_MAP\ETCETCETC
While the actual hyperlink is shown as: ..\FINAL_DESTINATION_MAP\ETCETCETC

..\ replaces all the network routing till the map the excelfile is located in.


Now, normally after a crash, the hyperlink gets a fixed from showing from your C: drive, but I was only trying to get this to work for a next time I will have a crash, so I was working in a online enviroment that behaves a bit different :D

There were some links in it that were not changed from a previous break down. I fixed those easly by replacing
sOld = "c:\filefolder\excelfileloaction"
into:
sNew = "..\"



2017-02-24 13:04:11

Rebecca Buchanan-mackie

THANK YOU !!

We had a temp working on an Excel file that contained about 1500 hyperlinks to PDF copies of invoices. After she left we found all the hyperlinks redirected to her C:\ drive. This fixed it in a jiffy :-)


2016-12-22 23:56:58

Albert

Hello, can anybody recommend how to replace external links and update them to links from another directory? I'd like to put "existing" links in one range (say A2:A6) and revised links to (C2:C6). Is there a way for VBA to look at revised list and replace the existing links which are listed under A2:A6?

Anyone? Please!


2016-12-22 13:18:31

Angel

Fixed in 5 minutes.

Thanks!


2016-10-12 00:02:10

Hendrik

I have to agree with Chris B that the hours of time saved have been a godsend, I kid you not!, ( I have been trying to solve this problem for a week now!) and it worked in a few seconds! Minor programming ability required...Make a back-up file and try it. It Works!


2016-09-30 09:34:35

Albert

Hello, what about changing external links (not hyperlinks)? Is there a way to do it through VBA?

Thanks


2016-09-22 17:19:23

Brett Hartmann

I could use some help on this one as well. I tried creating a macro, then editing it and pasting this text in(I've never EVER used macros before) and I clicked run but it did not work. I have a folder on my desktop I use for Sunday morning church services that has my list of songs in it. The folder is called "Worship Songs" and it has Microsoft Word files in it. The excel sheet has hyperlinks to those word files. One day all of a sudden, my hyperlinks to the folder in my desktop were all broken. It looked like they had been replaced with something like this:

../AppData/Roaming/Microsoft/Excel/Worship%20Songs/There%20Is%20None%20Like%20You.doc

"There Is None Like You" is the name of one of the songs in the excel sheet and is the example I used.

Somehow, the hyperlink got changed to what you see above as well as every single song listed in the excel sheet. Can someone please guide me as to how to fix this? The above example didn't work. I ask that you please give me thorough and clear guidance, remembering that I have never used macros before. Please and thank you for your time.


2016-09-22 05:46:21

Sidd

Sub FindReplaceHLinks(sFind As String, sReplace As String, _
Optional lStart As Long = 1, Optional lCount As Long = -1)

Dim rCell As Range
Dim hl As Hyperlink

For Each rCell In ActiveSheet.UsedRange.Cells
If rCell.Hyperlinks.Count > 0 Then
For Each hl In rCell.Hyperlinks
hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
Next hl
End If
Next rCell
End Sub

Sub Doit()


2016-09-14 12:10:55

Conner Tynes

How about doing this but for each row individually and for it to actually open the file when you click the hyperlink like a PDF?


2016-09-14 07:00:45

Paul

This works a treat and just saved me hours of trawling through manually changing hyperlinks. Many Thanks.


2016-09-13 02:44:42

Aco

Thanks!


2016-09-08 08:03:31

sunanda

my folder is ABC and there many pdf files,the Excel sheet is out of the folder and hyperlink path ok,now I need Excel sheet put to in the ABC folder and how can edit and change the hyperlink path at once


2016-09-02 14:56:25

Ling

Thank you so much, save my life!


2016-08-10 20:42:37

Mark

Worked perfect!!

Can you provide code to have it cycle on its own through all the sheets?


2016-08-05 07:51:32

Claude Pelletier

Thanks for the code, much appreciated!

I added the following Input box:

sOld = InputBox("Change drive from:", "Data input", "your data", 1, 1)
sNew = InputBox("Change drive to:", "Data input", "your data", 1, 1)


I can change "E:" to "C:" and the link works great!

If I save the document "C:" becomes "../../../"

How can I save the document and keep "C:"?

Thank you!


2016-07-21 09:34:50

Kyle Golden

One problem I was having is in my search, a space had been replaced with %20, and the macro would not find the search string. When I replaced the %20 with a simple space, the string was found, and replaced with my new string.

Hope this helps someone.


2016-07-05 06:06:08

Willy Vanhaelen

@Swaroop

Use quotes instead of brackets:

"O:WorksSurveyAsset Management PlansPAW & ROW"


2016-07-04 02:00:15

Swaroop

can any one please help

old location (O:WorksSurveyAsset Management PlansPAW & ROW)

new location (O:WorksSurveyAsset Management PlansPAW & ROWROW images)

I wrote the macros but did not work. :(


2016-06-22 04:37:17

Hatem

You saved my life !! It is an excellent tool for saving a lot of time. I had to change more than 150 links in 10 worksheets. Awesomr work.


2016-06-14 13:15:02

mark puglia

THANK YOU


2016-06-13 08:52:16

John

Exactly what I was looking for. Many
thanks.


2016-06-03 14:06:47

Lainie

Thank you! this worked perfectly!!
and it was super easy to do. I have been cursing my spreadsheet for months for eating all my links. I thought that I would have to do them all by hand since they all point to specific files in a directory but I ran this with just the file path and left off the file names from the end and they all work again!!! YAY!!!


2016-05-25 03:27:29

Mostafa

Many Thanks. It works.


2016-05-06 06:22:37

Nick Corlett

I am moving 1000's of spreadsheets from one server to another and I want to bulk change all the links...

replacing \ExcelServer1 with \ExcelServer2

I don't want to do it on a spreadsheet by spreadsheet basis, is there a way of doing this?


2016-04-27 08:02:02

Susana

Hi,

some other people have the same problem, but I don't see anyone has answered.

Why does the macro only fix some of the links? I can't get it to change all the links in the active sheet.

This is what I did:

Wrong link: ..AppDataRoamingMicrosoft1.TRANSMITTALS1.INCOMINGTRFG-AOC-T-00184

Right link: ..1.TRANSMITTALS1.INCOMINGTRFG-AOC-T-00184

Macro:

Sub FixHyperlinks()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim SNew As String

Set wks = ActiveSheet
sOld = "AppDataRoamingMicrosoft1.TRANSMITTALS"
SNew = "1.TRANSMITTALS"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, SNew)
Next hl

End Sub

Like I said, it works... on some of the links.

Thanks for your help!


2016-04-22 04:02:29

Chris B

WOW - WOW - WOW - WOW
This worked - I had over 300 Hyperlinks on 8 different worksheets that, after, crash, all of them were changed to some roaming file on my c-drive.

This Macro worked and saved me hours of time.

Thanks so much for posting


2016-04-19 08:01:48

Ian Belmont

Great tip for Excel 2013.
Just fixed about 2000 hylinks - changing from a .txt file to a .htm file.
This is the first time I've used an excel macro.
Thanks
Ian


2016-04-13 16:15:26

MMI_dmj

Thank you, my first visit here is a great success!


2016-04-09 04:16:18

Nithin Nath E.N

Hi...
Worked like a charm....Thank You Verymuch. You saved me a lot of time


2016-03-03 09:50:32

Brian

When I try to run this, I get "runtime error 7, out of memory". When I hit debug, it highlights the h1.Address = replace(h1.address, sOld, sNew) line. Any ideas?

Sub Hyperlink()
Dim wks As Worksheet
Dim h1 As Hyperlink
Dim sOld As String
Dim sNew As String
Set wks = ActiveSheet
sOld = "c:UsersreceptionistAppDataRoamingMicrosoft"
sNew = "\tva-filesrv1dataOld G DriveLibrary"
For Each h1 In wks.Hyperlinks
h1.Address = Replace(h1.Address, sOld, sNew)
Next h1
End Sub


2016-03-01 12:22:55

Jockster

Thanks a LOT. Eventually got this to work. Issue i had was the "sOLD" i found was case sensitve when searching. Once i got past that it worked like a dream..


2016-02-29 04:40:07

Uros

THANK YOU very much!!
You save me so much work... :))

Best regards,
Uros


2016-02-17 06:47:49

Viral Patel

Hello Mr. Wyatt

Thank you for "Changing Huge Numbers of Hyperlinks" macro.
It really saved me.

Regards,
Viral Patel


2016-02-11 06:49:43

ensta

Hi,

Tried this script. Didn't work for me.
Getting a runtime error "out of memory".
Probably missing something re. setting up the macro and pasting the script.
Anyone got any tips?
Would be gratefully received. Even on macro basics.
Also, is there some directions on config for relative file locations? Rather than "d:" etc. Since the files are in the same folder as the XLS.

Cheers,
N*


2016-01-22 05:22:55

Clive Bailey

I tried the macro changing "c:" to "2016"
and "S:Network" to "2015". None of the links were changed.
A typical link address is as follows:
File:///\SuidseeserverOmegaOmegaDocumentsOmega Forms 2016KwotasieMagnadorKwotasie N15-1089 Pansegrouw - Diefwering.xlsx
I am trying to change the 2016 to 2015 which is where the documents are still located. When I created a new spreadsheet for 2016 all the links on worksheet 2015 changed to 2016.
Any suggestions?


2016-01-05 05:36:58

Jimmy Anderson

I'm changing part of the the Hyperlink in the main body of the address and it only appears to working on the active cell for me


2015-12-27 04:43:56

Phil Johnson

Wow, this is so cool, and it works. This is the first time I've used an excel macro. thanks very much.


2015-12-20 12:58:00

Ian N.

Great stuff, and - as others have commented - worked like a charm. My own reason for needing this involved structural changes at "the other end", where one of my sources had restructured its database. With ~17000 hyperlinks in multiple worksheets, I faced a daunting task even finding which ones needed tweaking, until I came upon your example -- after which, it was easy to adapt to my own specific XL2013 circumstances.

Thanks!

'old version = http://media.sitename.com/uploads/objects/rw/xxxxxx/type+object+name.jpg
'new version = http://www.sitename.com/library/frame.php?lid=xxxxxx#

For Each hl In wks.Hyperlinks
sOld = hl.Address
If Left(sOld, 12) = "http://media" Then
startpointer = Application.Find("/rw/", sOld) + 4
endpointer = Application.Find("/", sOld, startpointer) - 1
sNew = "http://www.sitename.com/library/object.php?lid= & Mid(sOld, startpointer, (endpointer - startpointer + 1))
hl.Address = Replace(hl.Address, sOld, sNew)
End If
Next hl


2015-12-04 11:14:47

Daniel Nyman

This Worked Excellent!
Thank You
Dan


2015-11-13 11:52:18

Toyese

Great stuff! Worked like a charm. Now my supervisor is going to wonder how come I was able to finish updating the spreadsheet so fast ;)

I came across a lot of codes online for this but this one is so simple and elegant. Thanks so much for sharing this. (y)


2015-10-26 07:57:31

Arvindh

Hello,

I would like to edit the URL based on the input value.

Example
I am copying the page into excel on weekly basis. So i would like to edit the URL by changing the dates. The option of changeing the dates should be by updating the values in the excel cell


2015-10-07 12:29:07

Willy Vanhaelen

@Rocky

This macro should do the job:

Sub FixHyperlinks()
Dim hl As Hyperlink
For Each hl In ActiveSheet.Hyperlinks
hl.Address = Replace(hl.Address, "oldfolder", "newfolder")
Next hl
End Sub


2015-10-06 15:50:28

rocky

i need to do a similar function but tweek it just a little, i have thousands of hyperlinks all in different folders, i've moved everything to a new folder, i only need to change part of the hyperlink path as the rest of the hyarchy/folder structure will remain the same

example

current hyperlink paths:
"C:oldfolderteststest1file0.ext"
"C:oldfolderteststest2file1.ext"
"C:oldfolderteststest3file2.ext"
"C:oldfolderteststest4file3.ext"

need changed to:
"C:
ewfolderteststest1file0.ext"
"C:
ewfolderteststest2file1.ext"
"C:
ewfolderteststest3file2.ext"
"C:
ewfolderteststest4file3.ext"

in this example i need to change begining of HL path from C:oldfolder to C:
ewfolder and then keep the rest of the previous path as the folder structure remains, any help would be greatly appreciated, thanks


2015-09-10 12:18:22

Andrew

Soooo helpful, thanks a bunch!


2015-08-13 09:20:44

Shaun

NVM...I see you found that;)


2015-08-13 09:18:54

Shaun

Paul,

Remove the first "Sub b()".
Its adding a line that the end is looking for. Also, to all who are having issues getting it to work, you have to put the directory as it is without the "file:///" . For instance, mine said "file:///C:whatever". When I entered just "C:whatever" it worked like a charm.


2015-07-09 05:50:49

Langisiewel

Thank you for this tip! It worked perfectly.


2015-07-07 13:03:04

Don

Mike J,
I have the same problem and my Hyperlink base was blank. All of my c: hyperlink references were changed to file:///c:usersdon.mainappdataroamingmicrosoftexcel*.*
I have tried putting c:\usersusername.maindocuments with and without the last slash, and with and without the file:/// in front into the Hyperlink base, but the macro refuses to work (no error messages) anyway. Any help?


2015-06-29 03:43:22

Derek

Hi,

I'd like to change the hyperlink in two locations within the hyperlink.

.....Cycle 93. SupplyAU Supply Review_Cycle 9.xlsx

to:

.....Cycle 103. SupplyAU Supply Review_Cycle 10.xlsx

Note that I want to change "Cycle 9" to "Cycle 10" in two locations etc. This identifier will be common across various folders which is why I only want to just change that value.

The example coding seems to change the last instance in the background hyperlink (though it didn't update the visual cell value).

Any ideas?

Thanks. Derek


2015-06-25 04:45:35

Paul

I couldn't get this to work until I realised that the hyperlink text, although shown in full when you hover over the link, had been truncated to

......xxxyyy

and it was this truncated text that the macro needs to look for, so in sOLD, I added in

......xxxyyy

and in sNEW I added the full UNC path with the change, eg

file:///\companyworkfolder1folder2xxxzzz

Now works fine. Thanks!


2015-06-10 17:03:32

mike j

knew things were too good! Back in Jan this macro fixed all my broken hyperlinks after an autosave, but I found an older tab that I did not fix (seems like the macro only works as written on the active sheet), and now when I try and fix that sheet, the macro doesnt work properly. It only fixes one cell at a time, and only if you've tried to click the hyperlink on that cell and gotten the popup warning that the link isnt found. I did find someone else who wrote what looks like a similar macro and got this result - fixing one link at a time and only if the spreadsheet "knows" the link is bad!

http://www.excelforum.com/excel-programming-vba-macros/1042370-macro-to-fix-hyperlinks-after-file-move-print.html

anyone any idea what is going on now?


2015-06-02 11:31:54

SamV

I have the code written below, but cant get it to do anything...i.e. I click run and save and all, but nothing actually happens to any of the hypderlinks.

Sub FixHyperlinks()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String

Set wks = ActiveSheet
sOld = "C:UserssviseurDocuments"
sNew = "C:UserssviseurDesktop"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
Next hl
End Sub

What do i need to do in order for this macros to update the worksheet correctly?

Also, the I just want to change the "base" of each hyperlink, as in the loaction, of multiple, different named files in the same location.

-let me know!

Thanks

Sam


2015-05-28 10:29:05

S Grieve

I'm new to coding and macros in excel. Can someone explain to me what the different stages mean as it doesn't seem to work when I try it out.

Or could someone tell me of a good way to test if it working or not.

Thank you


2015-05-27 09:39:55

Glenn Case

SD:

Suggest you search on "Debug Macro" for a number of suggestions on how to locate your issue. One thing I would suspect, however, is that the macro is actually working, but is finding no matches, i.e., you may have mistypes the "find" string, and therefore the macro finds no matches. It's easy to accidentally get an extra space inserted (or alternatively, not inserted), for instance, which may be very difficult to find. Regardless, stepping through the macro execution will help you pinpoint the area of difficulty.


2015-05-26 17:56:20

Randall

@ SD

sorry nvm i was wrong


2015-05-26 17:50:03

Randall

@ SD


Looking at what you posted sOld and sNew are the same values. So if it did work all you did was replace the old with the new which is the same as the old


2015-05-01 23:17:23

SD

Hi-thanks for posting the macro. Unfortunately, I need some help in making it work for me. I don't know what the error is. when I hit "run macro", it does nothing.

Sub FixHyperlinks()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String

Set wks = ActiveSheet
sOld = "C:UserssdDocumentsTAFQMay 2015charts05012015"
sNew = "C:UserssdDocumentsTAFQMay 2015charts05042015"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
Next hl
End Sub

What is wrong here?


2015-04-27 16:57:59

Ben

it worked for me too. thanks..


2015-03-26 17:10:26

Harry

This worked. Thanks for your help!


2015-03-25 07:46:10

David French

Hi Allen
Per comment by mike j 14 Jan 2015, 14:44

Following a PC crash I too have an Excel spreadsheet with many thousand hyperlinks that have all changed to referencing ........AppDataRoamingMicrosoft instead of a Dropbox folder as they should

Being new to Macros in Excel 2013 I have created a dummy test file & tried to test the process & get the macro to work in that
I have following your article instructions & read all of the comments

However the macro runs, but does nothing & shows no errors

What am I too doing wrong?

Thanks


Sub Hyperlink()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String
Set wks = ActiveSheet
sOld = "C:UsersHomeDesktop"
sNew = "E:temp"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
Next hl
End Sub


2015-02-19 02:35:34

Barry White

Hello Hassan,

Please try as shown below:

Sub Hyperlink()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String
Set wks = ActiveSheet
sOld = "c:Usersmohamed.hassanAppDataRoamingMicrosoft"
sNew = "E:1Phd0ThesisRev01"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
Next hl
End Sub

Regards


2015-02-13 14:03:49

Willy Vanhaelen

When the error occurs, click 'Debug'. The the line causing the error will be highlighted. That's already a big help.


2015-02-13 01:04:19

Hassan

The Macro is doing nothing and the message is
Run Time Error '7':
Out of Memory

Then 3 choices
End
Debug
Help

I really appreciate your help to overcome this problem


2015-02-04 13:43:49

Willy Vanhaelen

@Hassan
Then there is probably something wrong with the path's in sOld and/or sNew.

Anyhow you should be more specific about what is not working. For instance, do you get an error or is the result not what you expected or is the macro doing nothing...


2015-02-02 23:58:31

Hassan

I have tried as here below but not working as well, what did I do wrong? many thanks in advance
Sub Hyperlink()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String
Set wks = ActiveSheet
sOld = "c:Usersmohamed.hassanAppDataRoamingMicrosoft"
sNew = "E:1Phd0ThesisRev01"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
Next hl
End Sub


2015-02-01 06:47:59

Willy Vanhaelen

@Hassan
Replace: hl.Address = Replace(hl.Address, " ", "")
with: hl.Address = Replace(hl.Address, sOld, sNew)


2015-01-31 09:23:06

Hassan

I would appreciate your help. I did this but not working, What I have done wrong?
Sub FixHyperlinks()
Dim wks As Worksheet
Dim hl As HyperLink
Dim sOld As String
Dim sNew As String
Set wks = ActiveSheet
sOld = "c:Usersmohamed.hassanAppDataRoamingMicrosoft"
sNew = "E:"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, " ", "")
Next hl
End Sub


2015-01-29 13:45:58

Dean

As a follow up to my comment a few minutes ago, I redefined the hyperlink base, which only further ruined my hyperlinks (since it just adds to them and doesn't remove anything), but after doing that, the macro now works to replace all links and not just one or two. Honestly, I have no idea why. Nothing else has changed. But I'm all good now!


2015-01-29 13:23:41

Dean

I'm having the same problem that Jeff mentioned, where the macro will only update one cell at a time, but SOMETIMES it will do two or three. Any idea what's going on here?? Is something terminating the macro before it can finish?


2015-01-14 14:44:52

mike j

PERFECT SOLUTION!!! autosave after pc crash deleted my hyperlink base folder location and redirected all hyperlinks by creating absolute links to the autosave directory:
with this macro I replaced the absolute link path (all the text in the hyperlinks before my base folder, copied into sOld = "...." with sNew = "" (nothing) - but I also had to redefine my hyperlink base (in 2010, File, click on properties in right panel and choose advanced properties then at the bottom of the summary tab, voila there is the place to redefine your hyperlink base!


2015-01-09 01:00:57

sk

super!!!


2015-01-08 10:09:13

Karen

@Shawn

No I didn't use H1 - the code uses HL (hl) - as in hyperlink.

You said the code worked for you. Did you replace hl with h1?


2015-01-07 08:28:30

Shawn

@ Karen:

This may sound silly, but I noticed in your comment you said hl (as in "hL"). Have you tried using "h1" (one) instead?


2015-01-07 08:25:23

Shawn

I just wanted to give a huge thank you for this. I just updated a spreadsheet containing over 34000 hyperlinks to network drawings using your macro. I shudder to think of how long it would have taken to update each one individually. (Update required since our SharePoint site changed addresses.)

Your code worked flawlessly for me. Outstanding work!


2015-01-05 10:16:15

Karen

@Willy

I spoke too soon. Unfortunately some of the files that the hl's reference have spaces in them so some of the links don't work.

Any other suggestions?


2015-01-05 10:10:57

Karen

@Willy

Thank you so much!

When I removed the space and then changed the hl it worked like a charm!

You're the best :)


2015-01-02 13:48:45

Willy Vanhaelen

@Karen:

Read my post of 30 June 2014. The "%20" issue might solve your problem.


2014-12-31 11:46:58

Karen

I'm not having any luck at all, same as Guillermo. Could REALLY use some input.

Here's what I have:

Sub FixHyperlinks()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String

Set wks = ActiveSheet
sOld = "../../Wacky%20Files/KMP/"
sNew = "\PserverStaffShareKMP"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
Next hl
End Sub


2014-12-19 14:46:25

Charles Parent

You simply saved my life and A LOT of time.

Thank you so much! I owe you a beer.


2014-11-10 06:40:11

Guillermo

Hi,

I'm totally unexperted with macros in EXCEL but I need to run this macro. However, I have followed all the instructions and the macro button (assigned to this macro I created) is not working. Nothing occurs. I have no error messages of any kind. Any advice?


2014-10-24 17:39:39

Jeff

For some reason, the macro is only updating one cell. I have to try to open the bad link and then run the macro again to change the next address. This would not be so bad, but I have about 3000 addresses to change.
So close I can taste it...


2014-08-21 09:43:08

great help

thanks for the tip, it was very helpful.


2014-08-15 13:32:35

Sergio GDP

Excellent! Thank you for your help. Never used a macro before, yet your instructions were clear and the macro worked like a charm. Funny things is, the problem you described was identical to mine.


2014-07-28 09:30:08

Curt

Works great and with this tutorial on editing macros you'll feel like a MacroMaster when it works

http://office.microsoft.com/en-us/excel-help/overview-RZ102337714.aspx?CTT=1&client=1§ion=1


2014-07-15 17:31:56

Bill W

Worked like a champ.. THANKS!! Saved a lot of time.


2014-07-10 11:12:05

Conrad

Thank you SO much for this. Literally saved me a few days work. Awesome!!


2014-07-01 12:53:50

Nate

This saved me hours. Thank you thank you thank you


2014-06-30 07:26:59

Willy Vanhaelen

@Hunter:
The Replace function translates %20 to a space. So replacing sOld = "%20" with sOld = " " will do the job.

I simplified the macro a lot:

Sub FixHyperlinks()
Dim hl As Hyperlink
For Each hl In ActiveSheet.Hyperlinks
hl.Address = Replace(hl.Address, " ", "")
Next hl
End Sub

Note that hl.Address truncates everything after and incuding # (hash sign).


2014-06-27 08:50:32

Glenn Case

Hunter:

I expect the issue is the search string is a null string, which means that it will not be found since all your strings contain something. Try appending the %20 to whatever was in front of that; i.e., search for "Mydrive" and replace with "Mydrive%20"


2014-06-26 10:00:23

Hunter

I am trying to use the macro to replace %20 from hyperlinks that someone added and have no spaces between the names on the one sheet but it doesn't seem to be working.

It looks like it is running but none of the hyperlinks actually change, I am using Excel 2011 on a Mac if that helps.

Here is what I have so far:

Sub FixHyperlinks()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String

Set wks = ActiveSheet
sOld = "%20"
sNew = ""
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
Next hl
End Sub


2014-06-06 13:57:29

awyatt

Paul,

You lost a line from the original macro, just before the "End Sub" line:

Next hl

-Allen


2014-06-06 13:37:40

Paul

Sorry, I took out the Sub b at the start.

Now I get an "For Without Next" error.

Sub FixHyperlinks()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String

Set wks = ActiveSheet
sOld = "http://portal.mmc.gov/orgs/Controls%20Guide.docx"
sNew = "http://portal.mmc.gov/orgs123/123Controls%20Guide.docx"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
End Sub


2014-06-06 13:21:58

Paul

Allen,

This is my macro, but I get an error: "Expected End Sub" when I run it.

What am I doing wrong?

Thanks.

Sub b()
Sub FixHyperlinks()
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String

Set wks = ActiveSheet
sOld = "http://portal.mmc.gov/orgs/Controls%20Guide.docx"
sNew = "http://portal.mmc.gov/orgs123/123Controls%20Guide.docx"
For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
End Sub


2014-05-14 07:37:38

Disney

Was helpfull


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.

Links and Sharing
Share