Link to home
Create AccountLog in
Avatar of mshaffner
mshaffnerFlag for United States of America

asked on

MS Excel 2010 External Links

I have recently started using a new computer at work with Windows 7 / MS Office 2010.  I've converted one of my excel files to the new format.  This particular file uses external links to various files in various directories.  For some reason, every time I close and re-open the file, all of the external links point to the same directory the file is located in, causing broken links.  I do not see why this should not work as it did in the earlier version of Excel.  What can I do to keep the external links pointing to the correct directories?  Your attention to my inquiry is greatly appreciated!
Avatar of Jan Karel Pieterse
Jan Karel Pieterse
Flag of Netherlands image

Odd, this should not be happening.
Do you still have the original file?
ASKER CERTIFIED SOLUTION
Avatar of Stacy Brown
Stacy Brown
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mshaffner

ASKER

jkpierterse:  yes - it is odd, and yes, I do still have the original file

finalword:  i'm sorry I am a bit confused.  I think I am using absolute links.

Scenario:
Source File:  \\206.22.35.216\exec_share\Cash Flow.xls
Example Linked Files:  \\206.22.35.216\office_share\File1.xls

This has always worked fine for me until I've upgraded the source file to MS Excel 2010 (.xlsx).  Now the linked file is showing \\206.22.35.216\exec_share\File1.xls instead of the office_share directory.  I have no idea why Excel would take it upon itself to impose its all knowing power and change my formulas!  Please help!  I don't want to be using old versions of files forever!
You are using Relative links if the address changed.  You need to use Absolute links.  FYI... you can use find/replace to change these. You will need to show all the field codes and after you change them from exec_share to office_share then you will need to select the whole document and Update all the fields (F9).
How am I using relative links if the cell's reference the entire UNC Path to the file?  It is my understanding that relative links only reference the file's name and the data files must be opened before the source file is opened or located in the same directory as the source file.  Neither of which is required with my cell references using the Excel 97-2003 format.
FYI:  \\206.22.35.216\ is a network attached storage device.
If the link name changed to the folder where you document is located, the links are relative.  This is the default setting.
Though I'm unsure if I agree, how do you change that?  The article you submitted describes how to change from absolute to relative, not the other way around.
CTRL + A to select the whole document
ALT + F9 to toggle the field codes on
Use Find/Replace to find exec_share and replace with office share
Either go through and check each one or Replace All
CTRL + A to select the whole document
ALT + F9 to toggle the field codes off
CTRL + A to select the whole document
F9 to update all the fields
finalword: what you describe is Word, not Excel.

mshaffner: Open the edit links dialog and click the offending link. Now click Change source and navigate to the proper file and click OK.
jkpieterse....you are absolutely correct.  I'm so sorry folks.  We just had this exact same problem in Word and this is how we fixed it.

My apologies.
I see... The ALT + F9 was confusing me, as it did nothing in Excel.  I presumed you meant:

Ctrl + ~

but that didn't do the trick either.  I have tried to use the Change Source option in Edit Links, but it also did not work.  Every time the file is re-opened, all of the external links change to the same directory as the main file.  I will try it again to make sure I did nothing wrong and will advise.  Meanwhile, I'm accepting any other ideas.  If all else fails, I will just have to relocate all of the files to be in the same directory.
I haven't read this in full, but perhaps it gives pointers:

http://support.microsoft.com/kb/328440?wa=wsignin1.0
Please be advised that I am testing above mentioned possible solutions.  Please stand by for an update.  Thanks for your patience!
OK Folks:  Thank you all so much for your help!  I was finally able to convert the file and have it work as it should.  It seems that Microsoft Excel is refusing to allow UNC paths to files to maintain integrity upon conversion.  The only way around it was to change all of the UNC Paths to Mapped Drives.  Basically it sucks because now I have all of these mapped drives on my computer and I really don't want to work it that way, but it is what it is.  Thanks again for your help!