Solved

Moving Excel Files

Posted on 2003-10-21
5
619 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

738 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