Link to home
Start Free TrialLog in
Avatar of hypercube
hypercubeFlag for United States of America

asked on

Excel hyperlinks getting corrupted.

I have a large number of Excel files in one folder e.g. C:\Users\Me\Documents\Files\[*.xlsm]
One of the files is a summary of all the other files.  It's just a list of all the other files with certain cells summarized.  Let's call it list.xlsm.
In the first column are descriptive names of each file.
I have elected to add a hyperlink to these names that points to that file so I can open it easily.

Twice now, I've found the hyperlinks corrupted like this:

Original hyperlink:
file.xlsm

So, since list.xlsm is in the same folder, the implication of this link would be C:\Users\Me\Documents\Files\file.xlsm

This morning I opened the list.xlsm file and tried to use the hyperlinks.  They didn't work.
In opening the links for editing, I find them changed to this:

Resulting hyperlink:
../../../../../AppData/Roaming/Microsoft/Excel/file.xlsm

that's right -../../ and so forth and the following reference to AppData, etc. exactly.

This is the second time that this has happened.  I need to prevent this as I only know how to fix it with a tedious manual process.
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

When creating the hyperlinks don't use the full path, use the relative path. To add a relative path add .. before the file name.
To prevent it in the future goto
File > Options > Advanced > General > Web Options > Files and uncheck "update Links on save".
Avatar of hypercube

ASKER

I have a routine that will find a string and replace it .. in the hyperlinks.
But I'm wondering how to do that where I want to add a ".." string to the beginning.
Ah well, putting the ".." in front of the link makes it unusable.  So that's out it seems.
Also, I have lots of linked data in the same sheet that I *do* want to have the data updated.  Is there a conflict?
ASKER CERTIFIED SOLUTION
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
=HYPERLINK("C:\Users\Me\Documents\Files\test.xlsm","TextToShow")

That's a clever idea!
The disadvantage, which is undesirable but not unusual, is that it won't survive the files being moved to another folder or the folder name being changed.  
The advantage is that it can be Replaced much easier than running a script to change the hyperlink as it's in more "visible" code.

I wonder what would happen if you did this:
=HYPERLINK("test.xlsm","TextToShow")
?