Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent Excel putting full filename in external links on update
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent Excel putting full filename in external links on update
Can only suggest a couple things. Are all workbooks in the same directory?
Can you shorten the file names or sheet names? and, gotta ask.. Have you considered using Access? " wrote: 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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent Excel putting full filename in external links on update
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 Sean Timmons wrote: Can only suggest a couple things. Are all workbooks in the same directory? Can you shorten the file names or sheet names? and, gotta ask.. Have you considered using Access? " wrote: 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). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent Excel putting full filename in external links on update
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... wrote: Looks like I wont get anywhere - just discovered these responses: http://en.allexperts.com/q/Excel-105...ative-path.htm http://groups.google.ca/group/micros...12372b 96f699 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 . . wrote: 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 Sean Timmons wrote: Can only suggest a couple things. Are all workbooks in the same directory? Can you shorten the file names or sheet names? and, gotta ask.. Have you considered using Access? " wrote: 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). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent Excel putting full filename in external links on update
Look in Tools, Options, General, Web Options, Files, and UNcheck "Update
links on save" wrote: 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... wrote: Looks like I wont get anywhere - just discovered these responses: http://en.allexperts.com/q/Excel-105...ative-path.htm http://groups.google.ca/group/micros...12372b 96f699 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 . . wrote: 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 Sean Timmons wrote: Can only suggest a couple things. Are all workbooks in the same directory? Can you shorten the file names or sheet names? and, gotta ask.. Have you considered using Access? " wrote: 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Opening Protected EXCEL worksheets to update linked data | New Users to Excel | |||
How to update an external excel sheet | Excel Discussion (Misc queries) | |||
Data from Excel 2000 Worksheet with external links is not displayed when opened in Excel 2003 | Links and Linking in Excel | |||
upgrade to excel 2003 update broken links error | Links and Linking in Excel |