Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Links with the same filename?
Hi there, I'm hoping someone can help because this is driving me absolutely
crazy. Filename1: c:\alloc\apple.xlsx Filename2: c:\alloc2\apple.xlsx Filename3: c:\compareapples.xlsx Cell A1 in Filename1 = 100 Cell A1 in Filename2 = 200 Cell A1 in Filename3 contains formula: 'C:\Alloc\[apple.xlsx]Sheet1'!$A1-'C:\Alloc2\[apple.xls]Sheet1'!$A1 After all are closed and saved do the following: 1. Open Filename3 and verify that formula in cell 1 still looks like it is suppose to 2. Open Filename2 and verify that the formula in cell 1 (in Filename3) still looks like it is suppose to. 3. Save Filename3 and verify that the formula in cell 1 still looks like it is suppose to. THIS IS WHERE I FAIL! My formula now looks like this: =[apple.xlsx]Sheet1!$A1-[apple.xls]Sheet1!$A1 It seems that Excel 2007 cannot save external links with the same name even if they are in different directories. I can create two name ranges (ie. AppleAlloc and AppleAlloc2) and it works but that would mean over 360 names in my real life application. Excel 2003 handles all this well - is this a known Excel 2007 problem OR maybe it is a configuration issue? Any ideas, thoughts would greatly be appreciated. We recently upgraded and I am tasked with getting many of our 2003 files to work in 2007. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Links with the same filename?
On Jun 23, 8:34 pm, Serena595
wrote: Hi there, I'm hoping someone can help because this is driving me absolutely crazy. Filename1: c:\alloc\apple.xlsx Filename2: c:\alloc2\apple.xlsx Filename3: c:\compareapples.xlsx Cell A1 in Filename1 = 100 Cell A1 in Filename2 = 200 Cell A1 in Filename3 contains formula: 'C:\Alloc\[apple.xlsx]Sheet1'!$A1-'C:\Alloc2\[apple.xls]Sheet1'!$A1 After all are closed and saved do the following: 1. Open Filename3 and verify that formula in cell 1 still looks like it is suppose to 2. Open Filename2 and verify that the formula in cell 1 (in Filename3) still looks like it is suppose to. 3. Save Filename3 and verify that the formula in cell 1 still looks like it is suppose to. THIS IS WHERE I FAIL! My formula now looks like this: =[apple.xlsx]Sheet1!$A1-[apple.xls]Sheet1!$A1 It seems that Excel 2007 cannot save external links with the same name even if they are in different directories. I can create two name ranges (ie. AppleAlloc and AppleAlloc2) and it works but that would mean over 360 names in my real life application. Excel 2003 handles all this well - is this a known Excel 2007 problem OR maybe it is a configuration issue? Any ideas, thoughts would greatly be appreciated. We recently upgraded and I am tasked with getting many of our 2003 files to work in 2007. Thanks To the best of my experience, no version of Excel has ever done this well. Because it is a Windows problem (unable to identify files individually, won't use the unique path universally), not entirely Excel. Although, Excel can't even do it properly on a Mac, which doesn't have the Windows problem. The solution is to change the names. Add project initials, a date, something. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Links with the same filename?
Spiky wrote...
Serena595 wrote: ... Cell A1 in Filename3 *contains formula: 'C:\Alloc\[apple.xlsx]Sheet1'!$A1-'C:\Alloc2\[apple.xls]Sheet1'!$A1 Picky: there's no initial equal sign, =, so this isn't a valid formula. Less picky, the second external reference is into a file with an xls extension. If that were the case, you shouldn't have a problem. I'll guess your formula is actually ='C:\Alloc\[apple.xlsx]Sheet1'!$A1-'C:\Alloc2\[apple.xlsx]Sheet1'!$A1 After all are closed and saved do the following: 1. *Open Filename3 and verify that formula in cell 1 still looks like it is suppose to 2. *Open Filename2 and verify that the formula in cell 1 (in Filename3) still looks like it is suppose to. So at this point the formula has changed to ='C:\Alloc\[apple.xlsx]Sheet1'!$A1-[apple.xlsx]Sheet1!$A1 ? That's what would have happened in Excel 2003. 3. Save Filename3 and verify that the formula in cell 1 still looks like it is suppose to. *THIS IS WHERE I FAIL! *My formula now looks like this: =[apple.xlsx]Sheet1!$A1-[apple.xls]Sheet1!$A1 I'd guess your formula is actually =[apple.xlsx]Sheet1!$A1-[apple.xlsx]Sheet1!$A1 If so, this is a bug similar to this other Excel 2007 doozy, http://technet.microsoft.com/en-us/l...chNet.10).aspx "Trust Center: Macros Description: In Excel 2003, if you had nothing but comments and declaration statements in your Excel VBA code, Excel retained the comments and statements and saved them to the file. Office Excel 2007 no longer saves VBA code that includes nothing but comments and declaration statements. Word 2003 and PowerPoint 2003 both had the same behavior. Migration path: Excel VBA code that is attached to a workbook and that contains nothing but comments and declaration statements is neither loaded nor saved with the file. Very few workbooks are affected by this change. Users can work around this issue by adding a subroutine or function to the Excel VBA code." It seems that Excel 2007 cannot save external links with the same name even if they are in different directories. I can create two name ranges (ie. AppleAlloc and AppleAlloc2) and it works but that would mean over 360 names in my real life application. If this works, you could always use a table-driven macro, with this macro possibly stored in a different file, to create the range names. Create a table with intended names in the first column and the cell references (without initial equal sign) in the second column, then have the macro iterate through the rows in this table like so. Dim twb As Workbook, rntbl As Range, i As Long Set twb = <reference to file that should contain these names here Set rntbl = <reference to table here For i = 1 To rntbl.Rows.Count twb.Names.Add Name:=rntbl.Cells(i, 1).Value2, RefersTo:="=" & rntbl.Cells(i, 2).Value2 Next i twb.Save Excel 2003 handles all this well - is this a known Excel 2007 problem OR maybe it is a configuration issue? Any ideas, thoughts would greatly be appreciated. *We recently upgraded and I am tasked with getting many of our 2003 files to work in 2007. ... Upgrading BEFORE testing backwards compatibility - what a concept! Perhaps the people who decided to do this may gain some better judgment from this experience. To the best of my experience, no version of Excel has ever done this well. Because it is a Windows problem (unable to identify files individually, won't use the unique path universally), not entirely Excel. Although, Excel can't even do it properly on a Mac, which doesn't have the Windows problem. Wrong. This has nothing whatsoever to do with Windows and EVERYTHING to do with Excel. Any file given by its full pathname is UNIQUELY identified. Excel's problem is that it IGNORES the drive/directory path for open files. And that's why Mac versions of Excel also suffer from this. This 'functionality' originated on Macs since Excel 1.0 was Mac-only software, and Excel 1.0 could only run on 512K Macs, and 512K Macs had a SINGLE disk drive and a nonhierarchical file system, meaning a single directory and no subdirectories. Therefore, on those 512K Macs Excel 1.0 could only EVER have a single file with a given filename open at the same time. And what did the geniuses on the Excel 2.0 developer team do AFTER Apple had introduced the Mac SE with a harddisk and a hierarchical file system? Why grandfather this BRAINDEAD functionality so EVERY Excel vversion going forward could only open one file at a time with a given base filename. This was ENTIRELY Microsoft's design decision for Excel ALONE. All the other Office applications that can open multiple disk files at the same time can open multiple disk files with the same base filename. ONLY Excel is mired in mid-1980s file system functionality (apparently in perpetuity). The solution is to change the names. Add project initials, a date, something. Or use named external references. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Links with the same filename?
Unfortunately, I can't rename the files - the files HAVE to be named the same. If saving external links (with the same name) doesn't work in Excel 2007 (it does work for Excel 2003) my only option (that I see) would be to create distinct named ranges pointing to each of the apple files (for some reason Excel 2007 can save external file references with the same name when they are associated to name ranges). Any ideas/thoughts/work-arounds would be greatly appreciated. -T Thanks for the quick response. Although it wasn't what I wanted to hear ..... it does confirm that I am not the only person seeing this. Thanks, T "Spiky" wrote: On Jun 23, 8:34 pm, Serena595 wrote: Hi there, I'm hoping someone can help because this is driving me absolutely crazy. Filename1: c:\alloc\apple.xlsx Filename2: c:\alloc2\apple.xlsx Filename3: c:\compareapples.xlsx Cell A1 in Filename1 = 100 Cell A1 in Filename2 = 200 Cell A1 in Filename3 contains formula: 'C:\Alloc\[apple.xlsx]Sheet1'!$A1-'C:\Alloc2\[apple.xls]Sheet1'!$A1 After all are closed and saved do the following: 1. Open Filename3 and verify that formula in cell 1 still looks like it is suppose to 2. Open Filename2 and verify that the formula in cell 1 (in Filename3) still looks like it is suppose to. 3. Save Filename3 and verify that the formula in cell 1 still looks like it is suppose to. THIS IS WHERE I FAIL! My formula now looks like this: =[apple.xlsx]Sheet1!$A1-[apple.xls]Sheet1!$A1 It seems that Excel 2007 cannot save external links with the same name even if they are in different directories. I can create two name ranges (ie. AppleAlloc and AppleAlloc2) and it works but that would mean over 360 names in my real life application. Excel 2003 handles all this well - is this a known Excel 2007 problem OR maybe it is a configuration issue? Any ideas, thoughts would greatly be appreciated. We recently upgraded and I am tasked with getting many of our 2003 files to work in 2007. Thanks To the best of my experience, no version of Excel has ever done this well. Because it is a Windows problem (unable to identify files individually, won't use the unique path universally), not entirely Excel. Although, Excel can't even do it properly on a Mac, which doesn't have the Windows problem. The solution is to change the names. Add project initials, a date, something. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Links with the same filename?
Thank you both for taking the time to response. I'm grudgingly beginning to
create all the name ranges. Harlan you are right my post had a typo - that second one also had a .xlsx extension. Despite all of this .... the pros to upgrading have so far outweighed the cons. -T "Harlan Grove" wrote: Spiky wrote... Serena595 wrote: ... Cell A1 in Filename3 contains formula: 'C:\Alloc\[apple.xlsx]Sheet1'!$A1-'C:\Alloc2\[apple.xls]Sheet1'!$A1 Picky: there's no initial equal sign, =, so this isn't a valid formula. Less picky, the second external reference is into a file with an xls extension. If that were the case, you shouldn't have a problem. I'll guess your formula is actually ='C:\Alloc\[apple.xlsx]Sheet1'!$A1-'C:\Alloc2\[apple.xlsx]Sheet1'!$A1 After all are closed and saved do the following: 1. Open Filename3 and verify that formula in cell 1 still looks like it is suppose to 2. Open Filename2 and verify that the formula in cell 1 (in Filename3) still looks like it is suppose to. So at this point the formula has changed to ='C:\Alloc\[apple.xlsx]Sheet1'!$A1-[apple.xlsx]Sheet1!$A1 ? That's what would have happened in Excel 2003. 3. Save Filename3 and verify that the formula in cell 1 still looks like it is suppose to. THIS IS WHERE I FAIL! My formula now looks like this: =[apple.xlsx]Sheet1!$A1-[apple.xls]Sheet1!$A1 I'd guess your formula is actually =[apple.xlsx]Sheet1!$A1-[apple.xlsx]Sheet1!$A1 If so, this is a bug similar to this other Excel 2007 doozy, http://technet.microsoft.com/en-us/l...chNet.10).aspx "Trust Center: Macros Description: In Excel 2003, if you had nothing but comments and declaration statements in your Excel VBA code, Excel retained the comments and statements and saved them to the file. Office Excel 2007 no longer saves VBA code that includes nothing but comments and declaration statements. Word 2003 and PowerPoint 2003 both had the same behavior. Migration path: Excel VBA code that is attached to a workbook and that contains nothing but comments and declaration statements is neither loaded nor saved with the file. Very few workbooks are affected by this change. Users can work around this issue by adding a subroutine or function to the Excel VBA code." It seems that Excel 2007 cannot save external links with the same name even if they are in different directories. I can create two name ranges (ie. AppleAlloc and AppleAlloc2) and it works but that would mean over 360 names in my real life application. If this works, you could always use a table-driven macro, with this macro possibly stored in a different file, to create the range names. Create a table with intended names in the first column and the cell references (without initial equal sign) in the second column, then have the macro iterate through the rows in this table like so. Dim twb As Workbook, rntbl As Range, i As Long Set twb = <reference to file that should contain these names here Set rntbl = <reference to table here For i = 1 To rntbl.Rows.Count twb.Names.Add Name:=rntbl.Cells(i, 1).Value2, RefersTo:="=" & rntbl.Cells(i, 2).Value2 Next i twb.Save Excel 2003 handles all this well - is this a known Excel 2007 problem OR maybe it is a configuration issue? Any ideas, thoughts would greatly be appreciated. We recently upgraded and I am tasked with getting many of our 2003 files to work in 2007. ... Upgrading BEFORE testing backwards compatibility - what a concept! Perhaps the people who decided to do this may gain some better judgment from this experience. To the best of my experience, no version of Excel has ever done this well. Because it is a Windows problem (unable to identify files individually, won't use the unique path universally), not entirely Excel. Although, Excel can't even do it properly on a Mac, which doesn't have the Windows problem. Wrong. This has nothing whatsoever to do with Windows and EVERYTHING to do with Excel. Any file given by its full pathname is UNIQUELY identified. Excel's problem is that it IGNORES the drive/directory path for open files. And that's why Mac versions of Excel also suffer from this. This 'functionality' originated on Macs since Excel 1.0 was Mac-only software, and Excel 1.0 could only run on 512K Macs, and 512K Macs had a SINGLE disk drive and a nonhierarchical file system, meaning a single directory and no subdirectories. Therefore, on those 512K Macs Excel 1.0 could only EVER have a single file with a given filename open at the same time. And what did the geniuses on the Excel 2.0 developer team do AFTER Apple had introduced the Mac SE with a harddisk and a hierarchical file system? Why grandfather this BRAINDEAD functionality so EVERY Excel vversion going forward could only open one file at a time with a given base filename. This was ENTIRELY Microsoft's design decision for Excel ALONE. All the other Office applications that can open multiple disk files at the same time can open multiple disk files with the same base filename. ONLY Excel is mired in mid-1980s file system functionality (apparently in perpetuity). The solution is to change the names. Add project initials, a date, something. Or use named external references. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Links with the same filename?
You should examine your definition of the word, "wrong". Cause,
stating I am "wrong", and then agreeing with, and giving more details of one of the two points I made is more appropriately termed, "in addition", if you ask me. But, maybe you wrote your own dictionary. As for my other point: Windows (and therefore ALL applications) cannot recognize a file EXCEPT by its path/name. Perhaps you've never moved a file to realize this particular issue? I would recommend you remain in bliss and don't try it. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
External Links with the same filename?
wrote...
You should examine your definition of the word, "wrong". Cause, stating I am "wrong", and then agreeing with, and giving more details of one of the two points I made is more appropriately termed, "in addition", if you ask me. But, maybe you wrote your own dictionary. Context: Because it is a Windows problem (unable to identify files individually, won't use the unique path universally), not entirely Excel. Although, Excel can't even do it properly on a Mac, which doesn't have the Windows problem. Wrong. Still wrong. Let's pick this apart. 'a Windows problem (unable to identify files individually, won't use the unique path universally)' What do you mean by 'unique path universally'? Do you mean that a file in the first volume of disk(0) on one machine under the path \foo \myfile.xls and another file in the first volume of disk(0) on another machine also under the path \foo\myfile.xls would both be accessible as c:\foo\myfile.xls that therefore the drive/directory pathname is ambiguous? As for Macs, are you saying it's not possible to give the first volume on the first fixed disk on multiple machines the same name, e.g., my_disk? Sorry, but directory pathname sans drive qualifier is implied relative to the root directory of some disk volume. Windows drive letters are nothing more than aliases to mounted devices. If these Windows machines are connected to networks with DHCP servers, all those C: drives could be accessed as \\network-computer-name\volume-name \directory\pathname\... As long as computer names aren't reused, no ambiguity, so unique identification. Most files are accessed by drive letter rather than \\server\share due to the convenience of the former. But other than syntax, there's no difference between accessing files locally through Windows using drive letters and accessing files locally through Linux/BSD/Unix/etc using mount points. It's Excel, both under Macs and Windows, that ignores the drive/ directory path of open files. This has NOTHING to do with the underlying OS or file system. But perhaps the problem here is that you're unable to express yourself clearly in English. As for my other point: Windows (and therefore ALL applications) cannot recognize a file EXCEPT by its path/name. Perhaps you've never moved a file to realize this particular issue? I would recommend you remain in bliss and don't try it. How would any sensibly organized file system recognize files without using pathnames? By disk sector? By hunting through all sectors on a disk for a particular initial sequence of bytes that serve as a file ID? Just how slow an inefficient do you like your computing? Do you mean that if \\a\b\c\d.xls contains a link to, say, \\foo\bar \ugh\somefile.xls, and \\foo\bar\ugh\somefile.xls gets moved to \\now \for\something\completely\different.xls while \\a\b\c\d.xls is closed, you want \\a\b\c\d.xls to automatically detect this change the next time it's opened? Ain't going to happen, and it's a very good thing too, because if Excel and other applications didn't work this way, it'd be next to impossible to write a lot of models that refer to data files stored with generic pathnames, e.g., d:\data\current month \data.xls. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External links | Excel Discussion (Misc queries) | |||
Prevent Excel putting full filename in external links on update | Excel Discussion (Misc queries) | |||
How to make an external link to a filename defined by a cell value | Links and Linking in Excel | |||
How do I use a cell value as the filename in an external link? | Excel Discussion (Misc queries) | |||
How do I use a cell value as the filename in an external link? | Excel Worksheet Functions |