Solved

Prevent Excel from swapping mapped drives to UNC paths

Posted on 2010-11-08
10
6,459 Views
Last Modified: 2012-05-10
Fellow Experts, I need your help.

I'll be migrating the file server from Netware 5 to Widows Server 2003 this week. There are some problems though.

Let's say, that we have OLD_SERVER and its' network share  OLD_SERVER:/DATA is mapped as S:.
Then we have for example:
S:\Some_folder\test1.xls
S:\Some_folder\Excel\tes2.xls
test1.xls contains a hyperlink to the test2.xls.

I'm going to leave drive mappings and relative paths as they are, but I CAN'T change Windows' computer name (they'll have to differ).

However there are some problems with the Excel files. Even if I insert a hyperlink through a mapped S drive, the program changes it automatically to UNC path (cell contains proper value, but the underlaying path leads to "file://\\OLD_SERVER\Some_folder[...]").
I've found an option in the Excel: Tools->Options->Common->network options->Files->Automatically update links (don't  know if it's a proper translation; I don't normally work with English Office suite).
It doesn't work though (same goes for some registry option in HKCU, implemented in Office 97 SR1).

I've found a KB:
http://support.microsoft.com/kb/328440

"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. "

It doesn't work that way however (as I mentioned above: the text in the cell is as should be, but underlaying hyperlink is resolved automatically to absolute path).

I'd appreciate any help.
0
Comment
Question by:marek1712
  • 5
  • 5
10 Comments
 
LVL 29

Expert Comment

by:pwindell
Comment Utility
You need to get rid of mapped drives anyway and go with the UNC pathes to begin with.  Mapped Drives are dinosaurs from the past that needs to be eliminated.
You can use MS's Distributed File System (DFS) to create a common Root,...regardless of what server the resource is on.
0
 
LVL 11

Author Comment

by:marek1712
Comment Utility
Hehe, explained common PC users, that they shouldn't use "drive letters" ;)
I haven't used DFS yet. Maybe my reasoning is wrong, bu can I create an alias, that'll allow files to work without any problems?
0
 
LVL 29

Expert Comment

by:pwindell
Comment Utility
My users are just as common as yours,...maybe worse.  They adjusted just fine.  I created a single UNC Shortcut (based on \\servername) , placed it in the Desktop Folder of the All User Profile on all the workstations.  Once opened all the Shares are listed,...Sharenames were done with meaningful names so that it is obvious what they are.  To the user it looks just like a "folder" on their Desktop.  Shortcuts like that can even be "browsed through" with most dialog boxes from Applications.  So it behaves just like any other folder on their local machine for the most part.  If you went with DFS then you just have that shortcut point to the DFS Root
DFS
I don't have any direct links handy but MS has several Documents for implementing DFS,..several are in Word Documents that are downloadable.   Googling for them should be much of a problem.  DFS has been around since NT4.0, but it has been improved with each new version of Windows.  It got to be really good (IMO) starting with 2003R2.
0
 
LVL 29

Expert Comment

by:pwindell
Comment Utility
One thing you can look at with DFS is DFS Replication.  With that you can have two separate Locations (typically on two differen servers) with identical contents.  So you would have a replcation point on a 'new" server,...let them sync up,...then remove the replication point on the old server,...so users start using the file from the new server transparently without ever knowing anything changed.   The DFS Root can even be on a server that does not even hold the files,...with the "folders" under that comming from other servers that actually hold the contents.  
You're better off digging up the DFS documents and studying them yourself,..my descriptions of things are not always that good.
0
 
LVL 11

Author Comment

by:marek1712
Comment Utility
I'd really like to use replication, but there's one issue I've mentioned in the question.
OLD_SERVER = Netware 5.
That means I have to deal with the trustees and such...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 29

Expert Comment

by:pwindell
Comment Utility
Yes,...Novel kinda screws you on that part.   But once you get the contents moved to the new server,...go with the UNC Shortcut instead of the mapped drive,...then your options expand out and you can figure out what works best for you from there,...maybe that includes DFS, maybe it doesn't,...but getting away from mapped drive letters is important.
BTW - guessing,...but maybe the reason Excel behaves as it does is because there is no guarantee that every user reaches the network location with the same drive letter,...so converting to the UNC location avoids that problem.  I have seen situtions where two departments use the same drive letter for completely different things,...so if they both go to the same file location on a server one might use H: and another might use J:.
0
 
LVL 11

Author Comment

by:marek1712
Comment Utility
In my case, every user has the same drive letter. "Anytime you open a drive, you see the same folder structure" - that's the reasoning I (/others?) follow,
In this particular case UNC paths don't solve the problem, as
\\OLD_SERVER\DATA\Some_folder\test1.xls
is not equal to
\\NEW_SERVER\DATA\Some_folder\test1.xls
And that's where drive mapping comes in handy... At least in theory.
0
 
LVL 29

Expert Comment

by:pwindell
Comment Utility
Well first,...either way,...you still have to get the material moved to the new server it is going to be on.
After that you are going to have to delete and recreate the mapped drive for all the user,...or you copy a UNC Shortcut to the Desktop of the All User's,...or you can even do both at the same time, and they will both work at the same time, neither gets in the way of the other.  But at some point you need to decide if you want to keep playing with the dinosaurs of mapped drive letters (a concept foisted on the world by Novel) or move into the future.
The Excel files are going to work better with network pathes,...simple as that.   The DFS helps keep the network paths consistant,...so the next time you introduce a new server,...the network paths stay consistant via DFS.
0
 
LVL 11

Accepted Solution

by:
marek1712 earned 0 total points
Comment Utility
Sorry for late reply.
It seems MS' KB is correct. I've finished two similar migrations throughout the month and all the files are working correctly..
@pwindell - thank you for your effort.
0
 
LVL 11

Author Closing Comment

by:marek1712
Comment Utility
The Knowledgebase article I've provided was correct.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A common practice in small networks is making file sharing easy which works extremely well when intra-network security is not an issue. In essence, everyone, that is "Everyone", is given access to all of the shared files - often the entire C: drive …
Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

763 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