Solved

Moving Excel Files

Posted on 2003-10-21
5
615 Views
Last Modified: 2010-05-18
Hi Guys,

I'm looking at moving a large set of excel files from one machine to another.  I need to move them in a way that ensure all the linkings continue to operate.  The structure is not changing, i.e "foldera\sunfolderb" etc will continue to be "foldera\subfolderb" albeit on a different server.

Upon testing i've found that the links seem to change but not the complete path - e.g.

I move;
\\servera\"foldera\subfoldera\filea"
to
\\serverb\businessa\departmenta\"foldera\subfoldera\filea"

(" " for illustration of structure only)

But the links only partially update; i.e

old path = servera\"foldera\subfoldera\filea"

newpath = serverb\"foldera\subfoldera\filea"

i.e the server changes, but not the remainder of the path

In some cases the links don't alter at all.

Obviously it is essential that the links continue to operate for the spreadsheets to work as intended, and I need to ensure that all links carry over.

All standard hardware etc, fully patched etc, I thinks this is not so much as a fault, but a knowledge thing so I havn't put many details of environment etc, but please advise should you believe they are of concern.

As always, all help is much appreciated.

Dave
0
Comment
Question by:davidsmith77
5 Comments
 
LVL 1

Accepted Solution

by:
SamB earned 500 total points
ID: 9592238
Hi Dave,

This seems to be because the "master" Excel file is still looking for the linked file(s) under the exact same directory on the new server. If you place it in a different directory (i.e. using the additional directory "businessa") then it can't find it.

You would have the same problem if you placed a directory before the linked file on your current server. You can however add more directories after the linked file but before the master file and this would be ok.

One way to solve this problem would be to have all linked files in the same directory as the master - then you are free to do what you want with additional directories. However, I guess this will not always be possible.

Another way would be to use VB to update the links for your master files - but this would obviously require some programming.

Otherwise I believe you may need to update the links (using Edit - Links).

Hope this helps....

SamB
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9596630
The easiest way ito have all the files open and then save them to the new server

If you save your files with all the links open then the links will be automatically set to the new locations

Cheers

Dave
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9599303
The only way I could think of doing it would be to hardcode the drive (instead of networkpath) G:\   as opposed to \\servera.  Setting up a DSN server would resolve the issue as well, rather expensive way of solving it though.
0
 
LVL 1

Expert Comment

by:plauwaer
ID: 9616917
For one file (with many links in it) You can change all the paths to the new server path by using search and replace \\servera\foldera to \\serverb\businessa\departmenta\foldera.

You can record this into a macro.

If you know VBA you can than let the macro run for all your files.

Patrick
0
 
LVL 1

Author Comment

by:davidsmith77
ID: 10062261
Thanks Guys,

Had to manually manage the process of performing per sheet updates.  Gave users ownership of their spreadsheets and via edit/tools updated all the links.

Not as painful as I first thought, though took some good detailed instruction and monitoring.

Thanks for your help.

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

863 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

27 Experts available now in Live!

Get 1:1 Help Now