Prevent Excel putting full filename in external links on update

[technicaltitch1]

Hi

I have a spreadsheet that has lots of links to another spreadsheet.
When I create and save it, it uses the filename with no path, and works
fine.

However when I close and reopen it, whether I update or not, if the
original file is not open, it adds the full path to all the filenames.
The formulae are then too long for Excel, and all the formulae become
errors.

Is there any way I can stop Excel doing this?

Thanks enormously for any help with this - this is my last ditch
attempt at saving a big voluntary Oxfam project...!!
Chris

PS. I'm using Excel 2003 on Win XP

PPS. The reason I need to do this is that the sheets are too complex to
put in one file - recalc takes several minutes.

PPPS. Possibly relevant - I create the sheets by cutting and pasting
sheets from a unified sheet, which automatically adds the filename
(without the path).

 

[technicaltitch1]

Thanks for the good responses. They are in the same directory. When it
is used live it will be on a network drive and potentially people's
laptops, so the path name may be long. I didn't use Access because
Oxfam staff don't have it as standard (and I was hoping users, having
used spreadsheets before, would be able to create their own reports in
Excel).

I'm currently looking for a way to save the formulae and freeze it, so
when re-opened, Excel can't change it. I was hoping to avoid needing a
server as volunteers administer the system in their homes, but looks
like I may have to redevelop it using some database.

Thanks again Sean for your time,
Chris

 

[technicaltitch1]

Looks like I wont get anywhere - just discovered these responses:

http://en.allexperts.com/q/Excel-1059/Linking-files-relative-path.htm

http://groups.google.ca/group/micro...6f699?lnk=st&q=&rnum=2&hl=en#a86e12372b96f699

I found that whenever I open the source file, the links become relative
again. Close it and the path is inserted (and there's no way I can stop
it doing this), breaking the formulae.

From the above discussions:

"Excel's external reference syntax/semantics preclude you from having
more than one file open at a time with the same base filename. When the
file is open, *only* the base filename in brackets appears in the
external reference. When the file is closed, Excel prepends the drive
and full directory path to the bracketted base filename, where the
drive and full directory path are where that file was more recently
saved or accessed, whichever is more recent. It's that rather braindead
semantics that makes Excel so much more difficult to work with than 123
or Quattro Pro or StarOffice Calc or . .

 

[technicaltitch1]

The good news is Excel 2007 seems to allow >8000 characters in a
formula (instead of 1024 in v2003).
(http://visio.mvps.org/Excel_2007.htm)

So I'm looking for a temporary workaround...

 

[Bob I]

Look in Tools, Options, General, Web Options, Files, and UNcheck "Update
links on save"