hyperlink file address changed

Question

I have been using an Excel 2007 spreadsheet that contains approx 500 hyperlinks connected to drawing files ( mostly pdf format ) located on a local server. Recently, almost all of the hyperlinks now point to my local drive " //C:\Documents and Settings\name\Application Data\ .... Abcd.pdf " whereas they used to be linked to " Y:\Data_Files\Drawings\Company\ Abcd.pdf " , where " Y:\ " is a mapped drive being the server.  About 95% of the hyperlinks no longer work. The Excel file is also located on the server, not on my local drive. Any suggestions to fix this and prevent it in the future would be greatly appreciated.  Thanks in advance.

Answer 1

Hi,

Under office Botton (upper left) / menu : Prepare / Properties /

and finallay : Document properties / advanced properties.../ Field : Hyperlink base

"er_precis" a écrit dans le message de groupe de discussion :

*** Email address is removed for privacy ***...

Thank you for the suggestions. I am trying to set the file location

info as you recommended, but can't seem to find what you describe.

I'm looking in the Excel Options, Advanced ,but I'm not seeing

anywhere to set file location info. I must be overlooking something.

Answer 2

Saved from a previous post:

A few people have said that this has stopped a similar problem from occurring. Maybe it'll help you--but you'll have to test it to make sure.

In xl2003 menus:

File|Properties|Summary Tab|Hyperlink Base

change it to C:\

(something that's always available)

In xl2007:

Click on the Office button

Choose Prepare, then properties.

Then use the arrow on the Document Properties dropdown

Choose Advanced Properties

And then change the hyperlink base on the Summary tab.

I like to use the =hyperlink() worksheet function for this.  I've never had one

of these worksheet functions change.

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.

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

er_precis wrote:

I have been using an Excel 2007 spreadsheet that contains approx 500 hyperlinks connected to drawing files ( mostly pdf format ) located on a local server. Recently, almost all of the hyperlinks now point to my local drive " //C:\Documents and Settings\name\Application Data\ .... Abcd.pdf " whereas they used to be linked to " Y:\Data_Files\Drawings\Company\ Abcd.pdf " , where " Y:\ " is a mapped drive being the server.  About 95% of the hyperlinks no longer work. The Excel file is also located on the server, not on my local drive. Any suggestions to fix this and prevent it in the future would be greatly appreciated.  Thanks in advance.

--

Dave Peterson

Answer 3

I believe this to be a network issue to do with roaming profiles and default file locations.  

Sorry to say, but it's not a network or roaming profile problem.  I have a Win XP machine with Office 2007, my hyperlinked files are on D:, which is a local, permanent drive, and they get rerouted to C: when this problem occurs.  Furthermore, this is a spreadsheet that originated in Excel 2000 or 2003, and only recently I saved it as a .xlsx file instead of .xls.  It is only since converting to from xl 2000/3 to xl 2007 that the problem started occurring.

As for changing the "hyperlink base" as many have suggested, this will probably work for the files on my D: drive, but what if later on I need to create a hyperlink to a file on C:?  If the problem is changing the path to the base, then the hyperlink for the file on C: will be rerouted to D:.  At best, changing the hyperlink base property is a bandaid that is likely to fall off at some point  in the future.  It would be much more effective if MS would just fix the bug.

Answer 4

Thank you for the suggestions. I am trying to set the file location info as you recommended, but can't seem to find what you describe. I'm looking in the Excel Options, Advanced ,but I'm not seeing anywhere to set file location info. I must be overlooking something.

Answer 5

I believe this to be a network issue to do with roaming profiles and default file locations.  You may find it useful to set the file location information in the advance setting on the Office button options to point at your previously mapped drive, if that is where you want your files to be saved and accessed.

Dave's solution that refers to the use of Hyperlink() function does overcome your problem if you have a need to point the default file location somewhere other than the old mapped drive Y.

---

Pat PS If you found this useful please vote. Thank you:¬)