• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Linked excel files - server migration

I'm in the process of migrating data from a Win 2003 server to a new server.

I have already migrated a lot of the shares using Robocopy but leaving the mother of all shares until last (that I am rapidly approaching!) due to it's size and complexity.

The share contains lots of complex excel sheets which usually wouldn't be a problem with Robocopy but some of them have active links to other spreadsheets (within the same share)

My question is, when I use Robocopy to migrate this volume. When it's live on the new server (with a different server name) will the links in excel need to be re-established as they are looking at a file path that contains the old server name?

I'd like to establish this prior to migration as if the links will be lost then I need to notify the teams! Unless there is a way around it or a tool I could use?

Thanks
0
Nick Smith
Asked:
Nick Smith
  • 3
  • 2
  • 2
1 Solution
 
Danny ChildIT ManagerCommented:
Most Excel links are coded using drive letters

Here's an example from a random sheet of mine:
=VLOOKUP(A306,'T:\IT\[FileName.xls]Sheet1'!$A$2:$B$303,2,FALSE)
which is then shortened once that file is opened:
=VLOOKUP(A306,'[FileName.xls]Sheet1'!$A$2:$B$303,2,FALSE)

So, if your folder structure is the same, and your drive mappings are the same, you should be fine.

Excel CAN work with UNC paths - \\Server\Share\Filename.xls - but this is not natively how it builds paths, unless your users make a habit of connecting to resources via UNC in the first place.

More info here.
http://support.microsoft.com/kb/328440

I'd suggest asking your most knowledgeable and old-time Excel users to suggest a typically large and evil set of sheets, and to try a sample robocopy of that branch of your folder tree only.
0
 
Danny ChildIT ManagerCommented:
0
 
Nick SmithHead of ITAuthor Commented:
Thanks DanCh99, I'll give it a go tomorrow and report back!
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
Rob HensonFinance AnalystCommented:
I had a similar exercise a number of years ago when changing the folder structure on a server, basically needed to move all folders and subfolders down a level.

I just moved (cut & paste) within Explorer and all the links moved fine. As DanCh99 says, the links may look like they are using Drive letters but I believe they use the relative UNC path behind the scenes; if one file is linked to another file that is three levels up, then a folder at that level and three levels down; each of those with the same folder names, the link should stay relative.

Thanks
Rob
0
 
Nick SmithHead of ITAuthor Commented:
so despite the server names being different, it's should really matter as Robocopy will move the files and folders over with  identical folder structure and permissions?
0
 
Rob HensonFinance AnalystCommented:
As Dan says, try with a sample first before doing the whole lot.
0
 
Danny ChildIT ManagerCommented:
The MS article says that Excel uses linked paths, which are not as utterly name-dependent as a proper UNC path is.   So, it's pretty robust, inherently.

If I remember correctly with Robocopy, it has a "text" option in that it will do a dry-run by creating a text list showing the source and destination of every file.  It does this *without* actually doing the moves.  The reason I mention this is that it could be useful in case you have errors later on.  In that case, you could refer back to the text file, and use it in a Find/Replace macro where it could search for "old" paths and replace with "new".

The last possible problem scenario would be for Excel files in OTHER locations that use links to the files on the server that you're moving.  Same rules would apply though - connections via drive mappings should be fine, it's just explicit UNC paths that could be awkward.  
Hope this helps,
cheers, Danny
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now