Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Prevent Excel from swapping mapped drives to UNC paths

Posted on 2010-11-08
10
6,709 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
ID: 34085451
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
ID: 34086083
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
ID: 34086400
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
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 29

Expert Comment

by:pwindell
ID: 34086468
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
ID: 34086673
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
 
LVL 29

Expert Comment

by:pwindell
ID: 34086996
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
ID: 34087057
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
ID: 34087205
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
ID: 34205102
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
ID: 34228592
The Knowledgebase article I've provided was correct.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

861 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