Hyperlinks in Excel changing to UNC Paths

Hi Experts,

Hopefully you can help me with this

Some of our terminal server users use excel spread sheets that have hyperlinks to other files

Now their mapped drives are the same, but instead of using the mapped drives for the links, it is using the UNC paths, which are not allowed via GPO

Even manually re-pathing the files doesnt work as it changes back to the UNC path

Any ideas how to resolve this?
LVL 4
Graham HirstIT EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

You could use either explicit references for the hyperlinks using the in-cell function:
=HYPERLINK(...)

Alternatively, set the "Hyperlink base" setting within the [Summary] tab of the individual File Properties dialog to, say, C:\.

BFN,

fp.
0
VirastaRUC Tech Consultant Commented:
Hi,

It can be resolved by turn off File "update Links on save".  

How to get there in excel 2010:

File > Options > Advanced > Scroll down to "General", then Click "Web Options" > Files, then uncheck "update Links on save".

Hope that helps :)
2
[ fanpages ]IT Services ConsultantCommented:
PS. For reference:

[ http://support.microsoft.com/default.aspx?scid=kb;EN-US;328440 ]
---
Mapped drives vs. UNC

When a source data workbook is linked, the link is established based on the way that the workbook was opened. If the workbook was opened over a mapped drive, the link is created by using a mapped drive. The link remains that way regardless of how the source data workbook is opened in the future. If the source data file is opened by a UNC path, the link does not revert to a mapped drive, even if a matching drive is available. If you have both UNC and mapped drive links in the same file, and the source files are open at the same time as the destination file, only those links that match the way the file was opened will react as hyperlink. Specifically, if you open the file through a mapped drive and change the values in the source file, only those links created to the mapped drive will update immediately.

The link displayed in Excel may appear differently depending on how the workbook was opened. The link may appear to match either the root UNC share or the root drive letter that was used to open the file.


Scenarios that may cause links to not work as expected

There are several circumstances in which links between files can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.

Scenario 1:

    You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
    You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.
    You open the file by a UNC path.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1. In other words, the Share name is eliminated from the path.

Scenario 2:

    You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
    You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \\Server\Share\Folder2.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1. For more information about what happens when you update a link in an Excel workbook, click the following article number to view the article in the Microsoft Knowledge Base:

817038 When you update a link in an Excel workbook, the link in a second workbook updates
---
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Graham HirstIT EngineerAuthor Commented:
Hi Guys,

Cheers for the suggestions, unfortunately the changes occur immediately and not upon being saved?

Still not sure why the mapped drive, reverts to unc rather than the mapped path?
0
Graham HirstIT EngineerAuthor Commented:
The end result im after is that they can carry on attaching links to their own files, that are stored on their mapped drives, but without them using unc as obviously that displays the servername/ip of the file server, and if i have to enable UNC pathing it opens up an additional risk factor
0
[ fanpages ]IT Services ConsultantCommented:
Cheers for the suggestions, unfortunately the changes occur immediately and not upon being saved?

Yes, I realised that; hence my two suggestions.

Do you try both?
0
Graham HirstIT EngineerAuthor Commented:
Hi FanPages,

Apologies, the in-cell function isn't suitable as it would mean we have to modify all users hyperlinks, both previously entered (for which there are alot) and all future hyperlinks added.

As for the summary tab, i cant find it that against the file properties? Also in this case they have two mapped drive that data may be referenced in, and as such pointing to only one would not resolve the issue completely
0
[ fanpages ]IT Services ConsultantCommented:
...Any ideas how to resolve this?
Apologies, the in-cell function isn't suitable as it would mean we have to modify all users hyperlinks, both previously entered (for which there are alot) and all future hyperlinks added.

Although that would resolve your issue; that was, in fact your question :)

As for the summary tab, i cant find it that against the file properties? Also in this case they have two mapped drive that data may be referenced in, and as such pointing to only one would not resolve the issue completely.

The fact you use mapped drives should not be affected by the "Hyperlink base" being set to something that is constant on every PC that opens the workbook (in this case, the root of drive C:).

If you let me know which version of Microsoft Excel are you using, I can direct you to view the Properties of the workbook File (as seen within the attachment).

File PropertiesBook1-Properties.png
0
Graham HirstIT EngineerAuthor Commented:
Haha fair dues :-)

But if the a spreadsheet has links to two different map drives won't that affect it as it'll only reference the one?

Also going forward the problem would exist for any new spread sheets that wish to include links unless manually altered?

It's office 2010 :-) I'll see if I can find the tab when I can view the files tomorrow
0
Graham HirstIT EngineerAuthor Commented:
Hi Fanpage,

Summary doesn't appear still against the property of the file
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

You need to look at "Advanced Documents Properties" in Excel 2007/2010/2013 (not the limited version that Microsoft seems to think is a suitable replacement).

I do not have Office/Excel 2010 installed presently, but I believe that if you click the "File" menu, then click the "Info" menu item, then the "Properties" button, you should then see a link for "Advanced Properties".

BFN,

fp.
0
Graham HirstIT EngineerAuthor Commented:
Found it, its in the file itself

As per my previous question though if the a spreadsheet has links to two different map drives, wont this be problematic?

Also going forward the problem would exist for any new spread sheets that wish to include links unless manually altered?
0
[ fanpages ]IT Services ConsultantCommented:
My previous response:
The fact you use mapped drives should not be affected by the "Hyperlink base" being set to something that is constant on every PC that opens the workbook (in this case, the root of drive C:).

The use of a local path is there to indicate that UNC paths should not be used.  The fact it is drive C: is not paramount; the fact it isn't a UNC or http:// prefix is.

Has the changed resolved your issue, or have you not tested this yet?
0
Graham HirstIT EngineerAuthor Commented:
Hi FanPage,

Yes I've made the change. And it does resolve the issue, but i have to now manually alter every link?

Is there any other solution? As the client has hundred of spread sheets, each with hundreds of links and the problem would also be present in future created spreadsheets?
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

You just need to update the "Hyperlink Base" File Property on every file, not every individual hyperlink in every worksheet in every workbook... unless you mean all the links that have already been changed & the workbooks have been saved with the updated references.

In you did mean updating every link that has already been changed then, no, there is no other way than visiting each individually, changing, & re-saving.

This could be automated, though, of course.

Also, changing the "Hyperlink Base" on each workbook file could also be automated, if you wished.

You could write some Visual Basic for Applications code that visits every workbook within a folder, & updates as follows:

{WorkbookReference}.BuiltinDocumentProperties("Hyperlink base") = "C:\"

For example, to change the "Hyperlink base" property of the currently active workbook:

ThisWorkbook.BuiltinDocumentProperties("Hyperlink base") = "C:\"

BFN,

fp.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Graham HirstIT EngineerAuthor Commented:
Why on earth is this so painful
You'd think if a hyper link referenced a G drive per say, if it was moved to another server it would try and open the file from the same link ie G drive

What would be the easiest way to update every link in a spread sheet?
0
[ fanpages ]IT Services ConsultantCommented:
What would be the easiest way to update every link in a spread sheet?

Here are two options (although other "Experts" may suggest further proposals):

a) Write an Add-in for MS-Excel that can execute a routine (on demand) that checks the address references of all Hyperlinks in the active (selected) workbook [every worksheet], or just the active (selected) worksheet only.  This routine would then either report on all hyperlinks discovered, or report on those with a predetermined format (for example, those that use Universal Naming Convention [UNC] paths, not local file system [LFS] paths).  The second part of the process (also defined within the Add-in) would be to visit each hyperlink discovered & update these accordingly.  Optionally, some hyperlinks could be removed from the initial report so that the second phase would ignore these.

b) Write a "one-off" (run once) routine that visits each workbook in a folder (& sub-folders, if necessary), updating all UNC path hyperlinks to LFS path hyperlinks, saves the individual workbook being tested, & then moves to the next workbook until all files have been updated (where appropriate).


Note: In both cases, the Local File System paths must be consistent for all users so that the network drive mappings (& the associated paths) read from the environment of the person using either of the above routines is applied correctly to support everybody else.
0
Graham HirstIT EngineerAuthor Commented:
Hey guys,

After much pain, we just enabled UNC paths. The user accounts are very locked down, so we just allowed them this one concession as it was proving too time consuming and troublesome for such a simple thing


Cheers for all your help guys
0
[ fanpages ]IT Services ConsultantCommented:
:)

You're very welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.