Moving Excel Files

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
LVL 1
davidsmith77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaveCommented:
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
leonstrykerCommented:
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
plauwaerCommented:
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
davidsmith77Author 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

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.