Moving Excel Files

davidsmith77
davidsmith77 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
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
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
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.

Commented:
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

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial