Solved

Hyperlinks in Excel changing to UNC Paths

Posted on 2013-06-03
19
4,703 Views
Last Modified: 2013-06-07
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?
0
Comment
Question by:FSIFM
  • 9
  • 9
19 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 9

Expert Comment

by:VirastaR
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 4

Author Comment

by:FSIFM
Comment Utility
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
 
LVL 4

Author Comment

by:FSIFM
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 4

Author Comment

by:FSIFM
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
...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
 
LVL 4

Author Comment

by:FSIFM
Comment Utility
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
Wish Marketing would stop bothering you?

Is your marketing department constantly asking for new email signature updates? Are they requesting a different design for every department? Do they need yet another banner added? Don’t let it get you down! There is an easy way to manage all of these requests...

 
LVL 4

Author Comment

by:FSIFM
Comment Utility
Hi Fanpage,

Summary doesn't appear still against the property of the file
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 4

Author Comment

by:FSIFM
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 4

Author Comment

by:FSIFM
Comment Utility
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
Comment Utility
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
 
LVL 4

Author Comment

by:FSIFM
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 4

Author Comment

by:FSIFM
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
:)

You're very welcome.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

OfficeMate Freezes on login or does not load after login credentials are input.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now