Hyperlinks changing

[XelaIrodavlas]

Hi all,

So I don't know if this is the right place to ask but I noticed this does appear to be a forum for Excel questions, despite this being the 'Access' Programmers forums (whacky right!? )

Anyway, A colleague of mine has a form with about a bazillion hyperlinks, all of which were added by right click > add hyperlink, no =Hyperlink() method i'm afraid.

We're now encountering a problem because the document is saved on our network, but while the links work fine on her machine they are rewriting themselves whenever i try to open it! the same happens when she sends the document via email.

This is resulting in something like this:
She sees > > > file:///\\servername\fdrive\folder1\docname.docx
I see - > > > file:///c:\users\alexsalvadori\folder1\docname.docx

As you can see it's removing the FDrive reference and replacing it with my user documents!

I have searched online and found a few tips but can't seem to get any working, Can anyone tell me how to tell excel to just leave the links alone?

my colleague is using excel 2007 while I am on 2013, but i dont think that's why excel is changing it(?)

Please help

thanks in advance,
Alex S

 

[Rx_]

The short answer? There is no way to stop Excel from doing the link updating. Microsoft discusses the problem in the following Knowledge Base article:
http://support.microsoft.com/kb/328440

The Knowledge Base article indicates the obvious: if a workbook is opened from a mapped drive, and the UNC refers to that same drive, the UNC in the link is updated to the mapped drive designation. Well..... Duh!
The article doesn't provide any actual solution to this problem. There is a fuzzy implication that the user could open the workbook using a UNC instead of a mapped drive. Trusting users to do this is not realistic.
One supposes the Excel Book could be made read-only and force people to save their changes at a different location. What a pain!
So, keep a Master copy with someone who follows the rules, them make a copy for everyone else to use? Better keep a backup copy too.

The only possible approach we've run across is to do away with the direct UNC references and use the INDIRECT worksheet function to build your references.
These would not be rewritten by Excel, but it does present other drawbacks. (For instance, the target workbook must be open in order for INDIRECT to fetch the linked information.)

People use Excel because it is available and easy.
However, it isn't the right tool for maintaining important data.
This is an opening to discuss MS Access. Very easy to use, more control.
If Access was used to enter new links with the associated data fields, a simple button could create the Excel (report) to a network location. Simple click of a button with a tiny macro.
This would serve to keep the data more controlled, secured, and provide distribution in Excel to the average user.

Does that explain why there is an Excel form in the Access web site?

Excel is a very portable common user tool.
When it comes to maintaining data important data, move the data storage to a tool suited for the job. But, distribute it back out in Excel.