Changing directories and linked Excel files


New Member
Sep 17, 2002

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!

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.

Upvote 0
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:

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

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

Or even
Target = C:NewFolderLastYearTarget.xls
Source = C:NewFolderLastYearDataSource.xls
Upvote 0
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!
Upvote 0
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.
Upvote 0

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)?
Upvote 0
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
Upvote 0

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.
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back