Look how old this is! I post at SearchCommander.com now, and this post was published 16 years 4 months 5 days ago. This industry changes FAST, so blindly following the advice here *may not* be a good idea! If you're at all unsure, feel free to hit me up on Twitter and ask.
If you’ve ever imported large quantities of URLs into a spreadsheet, or even copy/pasted just a few rows, one of the things that you’ve probably disliked is the fact that those URLs are not “live”.
In fact, in order to actually visit those URLs as needed, you have to copy/paste each one of them into the browser address bar, which takes extra time.
2011 Update – Watch this!
You may have engaged in the ridiculous process of double clicking into each cell and then pressing the enter key. This moves your cursor down the spread sheet line after line, making each URL miraculously “live”. I’ve even had my 11 year old son do this for hundreds of rows before.
You would THINK that Microsoft would be “smart” enough to make these URLs immediately live upon entry, but sadly; that’s not the case. Even with http:// before the URL, Excel simply cannot understand that it should be a link.
A couple of weeks ago, I gave a presentation at the Oregon Computer Consultants Association meeting, where I was reviewing some of the things I hate about office 2007, and the Excel Queen Karen Westermann seemed to think this would be an easy problem to solve.
Well apparently, it wasn’t quite as easy as she thought it would be. Since she’s the Queen though, she did come up with a solution, so here’s a spreadsheet for this miserable task that I can share with you all.
The Make my links live tool is available now, complete with directions here. My son doesn’t like it though, since he’s now out of a job…
Thanks Scott! This has been a frustration for me for a while now!
This is brilliant. Like you say, it’s a shame Excel can’t understand & do it itself. This is a problem I have come across regularly so this macro is really going to help me.
Thankyou.
Thanks Steve – I sure wish there was a a way to code it into the toolbar!
I don’t know whom to thank but I thank you all for this brilliant macro! Just one line saved thousands of hours since I had about 3000 rows of links to activate!
Just an ifno… I had to add to every row (link actually) the “http://” part in the beginning in order for the macro to work normally.
Thanks to the Queen but also to Scott for uploading it!
You’re welcome, and yeah, they do need http:// of course. Our macro wouldn’t add that because you have to have it in there in the first place – That’s my whole point! If you DO have http:// then Excel should simply recognize it, but it doesn’t…
Scott,
Thanks man. You’ve just saved me hours of work – this is simply brilliant.
Many Blessings
Paul
This if an excellent tool thank you
Nice tool your macro has a few improvements that can be made, this will allow for if a person uses one url and also will position the seloect all on the correct cell before starting
the cell a3 needs editing and the link to inhouse file needs to removed
cheers
– – –
Range(“a4”).Select
If Range(“a5”).Value = “” Then
Range(“a4”).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Else
Range(Selection, Selection.End(xlDown)).Select
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
Range(“A1”).Activate
End If
Thanks for the tip, and any time now, the new version will be released – it’s an add on, making a one click button rather than running a macro…
any idea why those links are often getting this error?
unable to open http://yadayada.com Cannot download the information you requested
This seems to be happening with my links almost all the time. It has nothing to do with the macro, the macro sounds very cool!
Thanks,
Cindi
that’s either going to be a bad internet connection or a bad URL call each and every time.
thank you very much for this helpful tool! it made work million times better.
Very neat tool.
Thank you for saving my day.
Stefan