[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Why souce location change in excel 2007

I have a user that created an excel model and linked the cells to multiple workbooks in the network drive. When he save the files and open it next time. the source location of the model has changed to "C:\Users\user profile\AppData\Roaming\Microsoft\Excel\".  

I can edit the links to correct the issue but have multple files like this. I want to understand why this happen and prevent it from happening again.

Any feed is greatly apprecitated.
0
jli168
Asked:
jli168
  • 3
  • 2
  • 2
2 Solutions
 
andrewssd3Commented:
It is likely because the original workbook was in the same folder as the linked files, or on the same network path.  Excel then stores the links as relative references - i.e. it finds they're from the same, folder, or one of its subfolders, or one of its parent folders.  Then when you save to a new location (e.g. your C:\Users....), it relocates the other links to a path relative to that.

It's difficult to avoid this, as it's all internal to excel, but one way is if the master file is on a drive letter path, e.g. N:\path1\path2\file.xls to define the links using a UNC path, e.g. \\server\path\path1\path2\file.xls, etc. In this way, Excel doesn't realise the linked files are in the same directory path and stores the references as absolute.
0
 
jli168Author Commented:
Thanks for the quick response.

My user create a new file and save it in s:\path\path1\file.xlxs and link the files to other workbook that are in s:\path\path2\file123.xlsx. File is saved and closed. Next time he opens the file the link will be sourcing to C:\Users\user profile\AppData\Roaming\Microsoft\Excel\. i don't understand why it will do that.

JL

0
 
regmigrantCommented:
the answer given previously explains it:-

because the first part of the path is the same "s:\path\" excel doesn't save "s:\path\path2" in the link, instead it save "..\path2", the ".." is a shorthand way of saying 'parent directory'. When the user reopens the file it gets copied to his roaming profile on local storage and the 'parent directory' is no longer "S:\path". Its a bit rubbish but its internal to Excel so there is nothing you can do to change it.

However:- if you train your user not to use the S:\path in the link but instead use a full UNC path of \\server\path\path1 and so on you will avoid excel reinterpreting the parent directory and it will save the full link. To make things easier for the user you can create a named shortcut in their 'network places' to fill the details which they should use instead of the mapped network drive.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
andrewssd3Commented:
I don't quite understand why that would be happening, unless he has saved his master file to the c: location - are you sure he is right about saving it to the network location?
0
 
regmigrantCommented:
The fact that its going to the roaming profile path is what makes me suspicious, I think he using offline folders
0
 
jli168Author Commented:
Hi Andrewssd3/regmigrant

My user link the cells from other workbooks when they are opened. He just click to it and link the cell. Does this is mean i have let tell my user to type the full UNC path all time when he want to link the cell? That will be a lot more work.

J
0
 
regmigrantCommented:

The other thing I would try is to try and stop people using 'mapped' network drives as much as possible when opening and saving files. you would use the same menu option but instead of browsing to a network location press the link underneath that says "sign-up for online storage or connect to a network location". They can access these links through 'web folders' or 'My network places' and use them the same way but, in my case at least, this seems to preserve the UNC.

If there are already lots of files with the problem the best solution in that case might be to create a macro to fix the links as the file is saved.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now