Changing Huge Numbers of Hyperlinks

wrt changing large numbers of hyperlinks, I have one that I have given up on. Someone may have a solution. Maybe it is simple.
For simplicity I have two worksheets. Sheet 1 is a calendar (a booking system). Sheet 2 has names and addresses etc in tables that go from left to right across the sheet. A hyperlink in sheet 1 links to the respective surname in sheet 2. There are hundreds of hyperlinks.
Sheet 2 fills the worksheet quicker than sheet 1 and when it reaches the right hand end of the sheet I remove many of the columns starting at A1 in sheet 2 and archive that file. This effectively shifts the tables to the left and creates more space, allowing more tables to be added. For simplicity let's say I removed 500 columns from sheet 2.
The problem this creates is the hyperlinks in sheet 1 now link to cells in sheet 2 that are 500 columns to the right. The rows are all correct.
Is there a macro that would tell these hundreds of hyperlinks to now link to cells that are 500 columns to the left?
I realize some will link to cells that have been removed but from what I can see the majority of them would be OK.

this method worked for me in perfect, thank you very much ^^

This worked fantastic! You saved me and my 5,000 hyperlinks. I cannot express my gratitude enough.

Also changed a lot of links but then got a Runtime error 7 out of memory so following to see if there is a fix

Works wonderfully, thank you!!

John

Same here, only one link at a time. Have you find the solution?

I have now tried this macro in orer to change a lot of hyperlink paths, but it doesn't work.

Private Sub CommandButton1_Click()
FixHyperlinks
End Sub

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

Set wks = ActiveSheet
sOld = "..\..\AppData\Roaming\Microsoft\Excel\"
sNew = "C:\Users\Jo\Documents\SaksDokumenter\"

For Each hl In wks.Hyperlinks
hl.Address = Replace(hl.Address, sOld, sNew)
Next hl

MsgBox "FIX utført"

End Sub

Only one link changes each time i run the macro.
It seems to be something wrong with the For-Next.

What am I doring wrong ?.

Thank you so much from me as well. Simple and working.
Well, it did in my small test file. But when trying to do 15.000+ conversions in one spreadsheet, it went "Run time error 7 - Out of memory"
Is that PC related or can it be fixed by altering the macro ?
Please help

Thank you so much from me as well. Simple and working.
Well, it did in my small test file. But when trying to do 15.000+ conversions in one spreadsheet, it went "Run time error 7 - Out of memory"
Is that PC related or can it be fixed by altering the macro ?
Please help

Such a simple macro, yet so powerful and helpful. Worked Great!!!

Thank you! I've been looking for this solution for a long time.

Thank you so much! Made my day so much better - can't believe this issue is still persisting after so many years.

Thanku so much, u saved my life.......

Hello Mr. Wyatt!
This topic was very useful for me and save me a lot of time!

THANK YOU!

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

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 = "..\"

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 :-)

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!

Fixed in 5 minutes.

Thanks!

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!

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

Thanks

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.

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()

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?

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

Thanks!

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

Thank you so much, save my life!

Worked perfect!!

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

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!

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.

@Swaroop

Use quotes instead of brackets:

"O:WorksSurveyAsset Management PlansPAW & ROW"

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. :(

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.

THANK YOU

Exactly what I was looking for. Many
thanks.

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!!!

Many Thanks. It works.

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?

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!

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

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

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

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

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

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

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

Best regards,
Uros

Hello Mr. Wyatt

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

Regards,
Viral Patel

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*

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?

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

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

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

This Worked Excellent!
Thank You
Dan

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)

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

@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

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

Soooo helpful, thanks a bunch!

NVM...I see you found that;)

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.

Thank you for this tip! It worked perfectly.

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?

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

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!

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?

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

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

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.

@ SD

sorry nvm i was wrong

@ 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

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?

it worked for me too. thanks..

This worked. Thanks for your help!

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

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

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

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

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

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

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

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

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!

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?

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!

super!!!

@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?

@ Karen:

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

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!

@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?

@Willy

Thank you so much!

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

You're the best :)

@Karen:

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

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

You simply saved my life and A LOT of time.

Thank you so much! I owe you a beer.

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?

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

thanks for the tip, it was very helpful.

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.

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

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

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

This saved me hours. Thank you thank you thank you

@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).

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"

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

Paul,

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

Next hl

-Allen

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

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

Was helpfull