why is absolute hyperlink in XLS file changed when file is saved

[SusanJ]

I created an XLS file with a VBA program from Access. One column in the file
has absolute hyperlinks of the format "file:\\server\share\path\filename".
The XLS file is subsequently copied with Windows Explorer to a different
location. This code has been used many times with no problems -- the
hyperlinks always work.

Now I have a user with a different experience: When she opens the XLS files
the links work but after entering some codes and saving the file the
hyperlinks no longer work -- they have been changed to
"../../../../server/share/path/filename" and spaces in the all names are now
"%20".

 

[Dave Peterson]

A few people have said that this has resolved the problem for them:

File|Properties|Summary Tab|Hyperlink Base
change it to C:\
(something that's always available)

======
I, personally like the =hyperlink() worksheet function, too.

I can put the UNC Path in a cell (hidden away) and put the filenames in column A
(if I have lots of links to use) and use a version of Gary''s Student's
suggestion:

With \\finance\statements\ in A1
and 1stqtr.pdf in A2
I'd put this in B2
=HYPERLINK("File:////" & $a$1 & a2, "Click Me")

Then when the IT folks move the files--or replace that server with a differently
named one, I can just make one change in $A$1 and all my links are working
again.

 

[SusanJ]

Dave, My user confirmed your solution (the Hyperlink Base) worked -- to our
mutual relief. Thank you very much.

SusanJ

 

[Dave Peterson]

Thanks for posting back.

Dave, My user confirmed your solution (the Hyperlink Base) worked -- to our
mutual relief. Thank you very much.

SusanJ