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.