Excel Help Please - Hyperlink location

TBirdUK · 2 Jun 2009 at 10:12

Hi all,

need some help (Excel) if possible. I have a large excel document which has loads of hyperlinks to files (emails) in it - now I need to move all the emails to a different drive. Is there any way to change all the hyperlink destination in one go or do I have to edit each one individually.

Cheers

Ciphon · 2 Jun 2009 at 11:06

As far as I'm aware your going to have to do this individually as you cannot open the details for multiple hyperlinks at the same time.

TBirdUK · 2 Jun 2009 at 11:13

Darn it - thats going to take forever, there must be a way of telling excel to search in a differenty directory for all hyperlinks, especially as the filename has stayed the same - I remember doing something where the link was broken and it remade the links automatically.

SourChipmunk · 2 Jun 2009 at 11:39

TBirdUK said:
I remember doing something where the link was broken and it remade the links automatically.

That's how I thought it worked. Something about rescanning for external links.

Copy your document to a different location and move a couple email message and see if it just works.

I'll scratch around the Microsoft KB to see if I can find anything.

TBirdUK · 2 Jun 2009 at 12:04

Thanks, I've tried moving all the documents to another location, and it just came up with the message that it could not open the file/link.

I've searched the web and there are loads os requests for this function, but I cant make head nor tail of the answers ie macros, tables etc - I would have thought this was a common problem with a smiple solution - I just cant find it.

SourChipmunk · 2 Jun 2009 at 13:26

Which version of Excel are you using, BTW?

edit: ahh, I see. It only works if you have External Source data imbedded. Just hyperlinks won't automatically update.

TBirdUK · 2 Jun 2009 at 14:07

I'm using Office 2003

Explicit · 2 Jun 2009 at 14:17

You can do this in a macro.

Can you post an example of the old location and the new location?

miniyazz · 2 Jun 2009 at 14:42

You might be able to use find and replace, finding the old path and replacing with the new path. Not sure if it works on hyperlinks though.

TBirdUK · 2 Jun 2009 at 14:56

Now all sorted, ended up finding a macro and adapting it, for those who are interested in "global" or "mass" hyperlink changes (thats for others search processes, it was as follows:

Sub ReplacePartHyperlinkAddress()
Dim hLink As Hyperlink
Dim wSheet As Worksheet

For Each wSheet In Worksheets
For Each hLink In wSheet.Hyperlinks
hLink.Address = Replace(hLink.Address, "c:\", "d:\")
Next hLink
Next wSheet
End Sub

Hope that helps someone else.

IAmATeaf · 2 Jun 2009 at 15:59

Bit late but may help

http://blogs.msdn.com/omars/archive/2004/04/29/123181.aspx

Just adapt to your version of Office.