Excel and the Twilight zone - Explain this!!

[MikeWar]

I have six Excel workbooks which are linked. All six workbooks are in the same directory.
When Excel is opened and all six workbooks are loaded the links are OK.

Except when I place them in a particular directory. When the full path is a certain path then
SOME links are broken. It appears that an old workbook has been substituted (but I am not certain).

I can fix/fail by simply renaming one of the directories in the full path and re-opening.
I can copy the workbooks to any other directory and they work in the new directory.

OK so the FAT is screwed up!
I removed every one of the workbooks, all copies, all versions from the hard drive.
I searched for the workbooks and removed every workbook and shortcut.
I used Regedit and removed every reference.
I turned off the fast-file-find.
I rebooted.

When I copied the workbooks back onto the drive, the problem was still there.

The only way I could get round the problem was to go back to an old set of workbooks and
add in the recent changes by hand (without using cut/paste).

Do Excel workbooks contain a history of paths or links?
Did I simply fail to do enough for the FAT to lose its references to the wrong workbook?
I have kept the errant workbooks in an isolation directory to be investigated at another time such as halloween, doomsday or full moon.

 

[KenWright]

Are they in a heavily nested set of folders?

What is the length of the full path plus the errant workbook names? There is a limit

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[MikeWar]

Thanks for quick reply.
The path is quite short
C:\Program Files\Directory1\Directory2
The workbooks are in directory2 when they fail.

Another factor:
I always use VB6 to open Excel and load the workbooks.
Several different VB6 programs can open the workbooks, but they all have the same result depending on the full path.

 

[KenWright]

Just as an example. If I take a new worksheet and put in a simple link to an OPEN file that is stored on my desktop, then I get a formula such as this:-

='[Price Model Small 2005.xls]2004'!$A$1+'[Price Model Small 2005.xls]2004'!$A$5+'[Price Model Small 2005.xls]2004'!$A$8

But, if I then close the source workbook, look what happens:-

='C:\Documents and Settings\kwright\Desktop\[Price Model Small 2005.xls]2004'!$A$1+'C:\Documents and Settings\kwright\Desktop\[Price Model Small 2005.xls]2004'!$A$5+'C:\Documents and Settings\kwright\Desktop\[Price Model Small 2005.xls]2004'!$A$8

If you have multiple references to the other files in your formula, then you will expand every reference by the length of the full file path.

Either:-

Reconsider your filepath, ie where you store them

or

Insert intermediate helper cells that reference parts of the external workbook and do what bits of yor formula would have done, and then reference those instead of the other workbook.

or

Consider using names in your formula to shorten it.

or

Try and make your fomulas more efficient, eg, summing cells using =SUM(Axxxx) is more efficient and much shorter than =Axx+Bxx+Cxx+Dxx etc, epsecially when it inserts the filepath for every external reference.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[MikeWar]

Thanks Ken
I've had a senior moment (again).
Yes, I once knew that the full path was replaced by the workbook name when the links were re-made. This was one of the observations I made when I had first encountered link problems (many projects ago).
Since I mainly use automation for linked workbooks I only see the workbooks after they are loaded, I never see the fullpath.
I forgot this happened.

I could have shut them down one at a time and checked the paths.
I can't easily open them up one at a time as it takes my VB6 program to remove security before loading into Excel.

To make the linking more robust and easier to test, I always pass values from one workbook to the other via a single sheet for I/O in each workbook. There is no calculation in any linked cell but I may do its error checking in an adjacent cell before passing to the rest of the workbook.

I believe I am familiar with the SUM function but thanks anyway.

Many thanks for your time.
Best Regards Mike

 

[MJPPaba]

In the example that Ken Makes

='C:\Documents and Settings\kwright\Desktop\[Price Model Small 2005.xls]2004'!$A$1+'C:\Documents and Settings\kwright\Desktop\[Price Model Small 2005.xls]2004'!$A$5+'C:\Documents and Settings\kwright\Desktop\[Price Model Small 2005.xls]2004'!$A$8

it would be more efficient to have a sum cell within the original document say A10, then link to that one cell from your external workbook.

that way the length of the formulae will only be on reference long rather than 3 times as long. i.e.

='C:\Documents and Settings\kwright\Desktop\[Price Model Small 2005.xls]2004'!$A$10

Same Cat. Different taxidermist.



crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[MikeWar]

Thanks MJPPaba I agree.
I think thats what I said in para 3 of my reply 3 Mar 05 6:45

My question is not about what do I do with linked cells but can anyone repeat or explain how the links fail/fix by simply changing one of the directory names in the full path?

To avoid any further diversions into what formula is in the cells, there is only one similar to = Book1!NamedCell.
These particular workbooks were created nearly two years ago and have been modified many times. They are used on at least a dozen PCs.
So some form of addressing corruption has occurred between one edit session and another.
I am trying for an explaination, only to understand/avoid the same problem in the future. I have gone back to a backup set which work.

Again many thanks for your interest.

Kind regards Mike