Relative path for location for links in Excel

[Patten]

Hi,

I have an excel sheet having cells linked to another excel sheet. My problem is, when I want to copy the set of linked sheets to another pc, I have to update all those links, since the directory structure where the files are placed on the second pc is not the same. My question: in the links (Edit->Links->Change Source), can I define a relative path, like ..\directory\linksheet.xls? This way, I could place my files in different directories, zip the stuff and unzip it with these same directories on another pc, and I should work fine, even if the "base" directory differs on one pc versus another one.

 

[GlennUK]

If you have your linked workbooks ( the workbooks containing the supporting information ) in sub-directories below the directory where the linking workbook is stored, then your copying will work the way you want. You can then do you zipping of the directory and all it's sub-directories, and unzip to another pc, even with a different "base" directory.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Patten]

Hi GlennUK,

thanks for your reply, however this is not working for me (Excel 2003, Windows XP). After unzipping the files to another pc and opening a file with a link in it, it asks for updating the link, since it is not finding the file the link refers to. And I want to avoid this asked update, since my files containing the link are to be protected: so the link is not updateble...
Other ideas, suggestions?

 

[KenWright]

Patten, yes you can do as you just said, eg

where my link would have looked like this with a mapped drive

='F:\Prog\BOEs\TTA\[654321-Prog BOE Sheet.xls]BOE TAB - FILL ME IN'!$A$1

As you say, the second I open the file from a machine that does not have that drive mapped to F then it just bombs. I could however just as easily put in the full UNC path (Universal naming Convention), which takes the form Server name \ share name, and represents the F: part of the link above. That gives me a new link of:-

='\\servdat01\findata\Prog\BOEs\TTA\[654321-Prog BOE Sheet.xls]BOE TAB - FILL ME IN'!$A$1

This should resolve OK no matter what machine it is opened on.

If you have a whole host of links currently that go to a mapped drive then you can just do an edit / replace to put in the UNC path instead. With my exampel i would simply replace F: with \\servdat01\findata

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[Patten]

Hi KenWright,

in fact it is not a matter of having mapped drives or not. It are completely other machines attached on another network. So I need relative paths, even without the server name in it.