This Is Broken has a post about hyperlinks in Excel and specifically about how to delete them. I checked the help files and sure enough the post is accurate. That bit of help truly is broken.
At the end of that post is a link to a product that will remove hyperlinks for you. It costs $20. I didn’t try it out, but I’m sure that product does a fantastic job, is user-friendly, and is valuable to those who buy it.
If I wanted to delete hyperlinks, I’d do this:
Sub RemoveHyperlinks()
Dim hl As Hyperlink
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each hl In ws.Hyperlinks
hl.Delete
Next hl
Next ws
End Sub
Dim hl As Hyperlink
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each hl In ws.Hyperlinks
hl.Delete
Next hl
Next ws
End Sub
But that may not catch them all.
Just to gripe more about Hyperlinks… I’ve never had a great experience with them.
Their Relative vs. Absolute quirkiness really gets me frustrated.
Whenever I need a hyperlink, I use the =HYPERLINK() formula, which works perfectly for me.
Rob
Dick
You don’t need to do it one by one – you can delete the whole lot on any one sheet in one go:
For Each ws In ActiveWorkbook.Worksheets
ws.Hyperlinks.Delete
Next ws
Richard
I’m really glad that Excel no longer opens files with the message “This workbook contains hyperlinks – do you want to update them?”
You may remember that happened with Excel 97: even though you had deleted all your hyperlinks to other files, the message kept appearing.
Unfortunately Excel 2003 is opening files with a similar message you cannot possibly get rid of : “Personal.XL locked for editing”. The message occurs each time you open a second instance of Excel.
While we are talking about Hyperlinks…
I hyperlinked a custom button on a toolbar to a file (right click -> customise -> Assign Hyperlink..)
I click on the button I get a warning message “Hyperlinks can be harmul to your computer….etc
“Yes / No”
Any way to get rid of this warning.
Also once the file is opened, I get the floating Web toolbar… any way of getting rid of this…
Frank –
“Personal.XL locked for editing”
If you convert your personal.xls file to an add-in, this will not happen. But then you lose the ability to call the functions and routines without having added commandbar controls. Easy tradeoff for me, don’t know about for others.
“At the end of that post is a link to a product that will remove hyperlinks for you. It costs $20. I didn’t try it out, but I’m sure that product does a fantastic job, is user-friendly, and is valuable to those who buy it.”
The post also mentions ASAP Utilities which is not only free but also contains lots of other user-friendly tools (I know ASAP has been mentioned in this blog before but I thought it was worth another mention for the “Remove Hyperlinks” tool)…
Jon
Absolutely, all my tools are in addins.
But they lock too. I write incredibly brilliant code in instance 2 from time to time, just to find that it refuses to save. Didn’t even warn me…
To Frank…
If you use windows explorer to mark your personal.xls as ReadOnly, then excel will not prompt you when opening the second instance.
And because you’ve marked it readonly, you’ll know not to edit any code in personal.xls!
The ExWorks EAT add-in toolpack contains a utility that not only deletes all hyperlinks in the selected range, but also creates hyperlinks in the selection, while automatically detecting the type of the hyperlink (internal to sheet or external to a file, website, email address,…)
This EAT add-in has a bunch of other utilities, is free and can be found on http://www.exworks.be.
Just wanted to give this heads-up.
Bart
I think hyperlinks are an under-utilized feature in Excel, especially in my new job, where files of 50 and more sheets are common. I’m sure a few hyperlinks would make navigating much less of a struggle.
About the Personal.xls message, I deleted the file from the Excel Startup directory, and to my surprise “Personal.xls locked for editing” has stopped appearing.
Thanks, Jon, for the add-in advice I’m sure that is the perfect solution for now I’m using a far less elegant way around personal.xls to create my application-level routine.
Jon,
You obviously don’t write brilliant code if you have an error, so get your head out of your ass.
Ouch!
Sorry Jon,
I didnt mean to write that to you. I meant to send that to Harald.
Ouch again.
i love you