External Links with the same filename?

[Serena595]

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

 

[Spiky]

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.

 

[Harlan Grove]

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

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/library/cc179167(TechNet.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."

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

...

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.

 

[Serena595]

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

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.

 

[Serena595]

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

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

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/library/cc179167(TechNet.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."

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

...

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.

 

[Spiky]

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.

 

[Harlan Grove]

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.