Changing directories and linked Excel files

[Bisleycat2002]

Hi

I have a number of Excel files - some of which may contain links and some don't which need moving to a new directory structure on teh same drive, I didn't construct the original files so I don't know what links are where.

Is there a best practice for moving the files quickly which will maintain the links - or am I condemned to re-establishing them afterwards?

Help and experience appreciated!

 

[stevebausch]

My lack of experience in this won't prevent me from opening my mouth

I will be watching this thread, you have good questions.

I assume there is a way to check for links, maybe it's right there, on the menus.

Once you can determine the files with links, everything gets easy.


(UP)

 

[Andrew Poulsom]

The links will adjust themselves if:

1. both source and target are in the same folder or

2. you preserve the basic structure.

For example:

Old
Target = C:OldFolderThisYearTarget.xls
Source = C:OldFolderThisYearDataSource.xls

New
Target = C:NewFolderThisYearTarget.xls
Source = C:NewFolderThisYearDataSource.xls

Or even
Target = C:NewFolderLastYearTarget.xls
Source = C:NewFolderLastYearDataSource.xls

 

[Bisleycat2002]

Thanks so far - however inevitably I wasn't clear enough - I am talking about hundreds of workbooks currently across multiple user's personal folders (on a shared drive) into a coordinated stucture (by year/topic). If I understand right this may involve opening each file individually and resaving to the new directory - ugh!

The temptation to use Explorer to shift them quickly is very strong but will surely unestablish the links!

 

[Andrew Poulsom]

If you follow the rules I outlined the links will be adjusted even if you move the files with Windows Explorer.

Basically Excel splits the link reference into 2 parts:

1. The folders common to both source and target.
2. The remaining folders containing the
source (and its name).

When you open the target the folders contained in 1 are updated by reference to the location of the target.

 

[Bisleycat2002]

Andrew

I think I understand - thank you however I am still not completely clear - am I expecting too much as a consequence of "the sins of my fathers" in linking files NOT in the same folders (albeit on the same drive)?

 

[Andrew Poulsom]

It really just depends on your new and old folder structure.

If the source is in a different folder from the target and you rename the folder the link will be severed. It's the same as if you renamed the source file.

Try it by copying (rather than moving) a few sources and targets about which you are uncertain into your new folder structure and see what happens.
This message was edited by Andrew Poulsom on 2002-10-10 08:10

 

[Studpure]

Hi,

I have multiple linked workbook that I moved from a personal drive to a shared drive using explorer.

When opening any of the linked files it prompts me with 'file not found' dialog and invites me to point to the right file.

What is interesting is when I keep on pressing Escape (cancel) all the files (the 200 of them) it finally gives me the right data linked on the shared drive.

So what is my problem? Well, each time I try to open a linked workbook I have to do the 'Press Esc button for thirty seconds' thing until it opens up...

Additionally, read-only users of the shared drive gets prompted only with a 'do you want to update files in this workbook. Yes or No. If they press no the don't have all the scenario mentioned above.

Any comments appreciated.

 

[Andrew Poulsom]

On the Edit tab of Tools, Options there is a checkbox "Ask to update automatic links". I suspect you have it unchecked and the other users have it checked.

 

[Studpure]

[No message]