Search sequence for referenced fiels

[Terry von Gease]

Does anyone know the search sequence used by Excel to resolved referenced
files?

Assume:

C:\\my_dir\ref.xls
C:\Documents_and_Settings\me\Application_
Data\Microsoft\Excel\XLSTART\ref.xls
C:\\my_dir\sub_dir\ref.xls
C:\my_dir\sub_dir\work.xls

work.xls contains a reference to ref.xls. It seems that the file in XLSTART
is the one loaded if it's there. In fact it seems to be loaded merely by
running Excel with no file specified while if it's in a different location
it seems to be demand loaded by opening the first workbook containing a
reference to it. But every now and then when openingwork.xls, a long winded
message to the effect the 'ref.xls. is already opened and another one cannot
be opened etc...

Sometimes not. I can't duplicate this at will, it just happens when it
happens. If I quit Excel and try again, sometimes but not always, it goes
away and things operate as expected. Sometimes moving the most recent
version of ref.xls into position straightens it out.

Could it be that if work.xls was last opened and saved loading a copy of
ref.xls that was more recent than the copy Excel decided to load it would
complain? This would be most inconvenient if it were the case.

Then it gets even trickier. Ref.xls has a reference to next_ref.xls which
may or may not live in the same place or places the ref.xls lives. I assume
that from a cogent answer to the first situation, the second will become
clear.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

 

[Bernie Deitrick]

Terry,

Since Excel defaults to using only the workbook's name whenever the
workbook is open, ignoring the path, it is a good practice to use
unique names for all your files.

Since I only use uniquely named files, I have never had the problems
you are describing.

HTH,
Bernie

 

[Terry von Gease]

The question is where and in what sequence does Excel search in order to
resolve references, not the wisdom or relative merits of one or another file
naming conventions.

Since you've never had the problems I'm describing does not mean that
singular file names is the answer to anything. In fact it has nothing to do
with this question.

It turns out that, upon closer inspection, the problem was that a button on
one of the sheets had the associated macro specified as a macro in the
referenced file, not in the actual work file which in turn would call the
procedure in the referenced file. Since Excel seems to keep full path
information on these macro assignments, it was trying to open another copy
of the referenced file, which made it throw up.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley