Solved

Moving Excel Files

Posted on 2003-10-21
5
614 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Copy exact format 27 99
IF OR formula Excel 8 31
Display multiple images in report 12 85
Oart.dll 2 30
How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

12 Experts available now in Live!

Get 1:1 Help Now