hyperlinks within Excel

[WICKed Lites]

Hi,

I created a spead sheet and used hyperlinks to link to other documents on
our server. This spreadsheet is now archived and has been moved to another
folder. The hyperlinks no longer work albeit, when you right click the
hyperlink it still displays the link (so you can remove, edit etc).

I thought the hyperlink would not work if you moved the document it was
pointing to and not the document containing the hyperlink.

There are about 400 links that no longer work. Is this just a blip or do I
have to re input all of the links (even though in the properties the path is
there??)

Thanks

Sam

 

[Dave Peterson]

I like to use the =hyperlink() worksheet function for this.

I'll put the path in a hidden cell (A1 in my example). (Include the trailing
backslash, too!)

Then put the filenames in A2:Axxx
Then use the =hyperlink() formula in B2:Bxx.

=hyperlink("File:////"&$a$1&a2,"Click me")
and drag down

Then when I need to change the folder, I can change it one location (A1).

I could embed the path directly in the formula:
=hyperlink("File:////c:\my documents\excel\"& a2,"Click me")
and use Edit|Replace, but that seems like more work to me.

========
If you used Insert|Hyperlink, then you've noticed that edit|replace won't touch
those hyperlink addresses.

Saved from a previous post:

If you used Insert|hyperlink (xl2003 menus), you'll have more work to do. But
the good news is David McRitchie has done most of it for you:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[WICKed Lites]

Hi,

thanks for your response but I really don't understand. I'm not sure how I
can use a hidden field. The spreadsheets links to, for example:

Document 1, document 2, document 3 and so on. The spreadsheet is filed in
folder x

The spreadsheet still links to document 1, 2 and 3, but the spreadsheet is
in folder Y. No hyperlinks work.

Surely using the =hyperlink() function wouldn't solve this problem or
rectify it happening again?

I'm lost!!!

SAm

 

[Dave Peterson]

If the path where the other documents are changes (the files are moved or the
share is deleted or the server is retired and the name not re-used), then by
using the =hyperlink() formula with a pointer to a single cell that contains
that folder name means that you only have to change the value in one cell. And
all 400 hyperlinks will be fixed.

If you used the insert|Hyperlink style of hyperlink, you'll have to either do
each individually or use a macro--like the one I suggested from David
McRitchie's site.

Did you try David's macro?