Question to Excel Gurus - Strange behavior of Excel links (2007 and 2003)

[Snimo]

-> It seems that 2 files having a same link (to a third file) cannot have 2 different values...

Let me show you with an example :

Create a file "Source.xlsx" : cell A1 contains 'TOTO'
Create a file "A.xlsx" : cell A1 contains a link to ='D:\[Source.xlsx]Feuil1'!$A$1
Create a file "B.xlsx" : cell A1 contains a identical link to ='D:\[Source.xlsx]Feuil1'!$A$1

Save and close all; then open again Source.xlsx and A.xlsx to change 'TOTO' to 'TITI'
Save and close all again

Now comes the fun !

Open A.xlsx; clicking "Do not update" : you can see 'TITI' (normal)
Without closing A, open B.xlsx (do not update neither) : you see 'TOTO' (normal)
switch back to A.xlsx, and now 'TITI' has become 'TOTO' !!!

Case 2 : without saving; cosoe all and open first B.xlsx; and then A.xlsx.
Now it's B.xlsx that has lost its initial value and has become 'TITI' (!!!)

What I see is : the value from the link of the file that has been last open replaces the value of all other identical links of other files...

Any idea to "solve" this ?

Simon

[Ken Puls]

I believe, and someone may correct me on this, that the key is here:

...Without closing A, open B.xlsx...

The prompting you're seeing is to fetch data from a CLOSED workbook, hence the prompt. When you have both workbooks open in memory, Excel will treat them as live links and update them automatically.

[Snimo]

Hi Ken,
I agree technicaly; but in the present case, my users need to see their Excel files "in the previous state" and compare them to a newer version (the one that has been updated...).
:-/

I didn't find (yet) any workaround; and was thinking of having at least a Warning -> a VB procedure would check if two open files share a same link and then would raise a warning popup...

As I don't have VB skills, I'll see if I can find someone...

If anyone has another idea, I'll be happy to hear about it

:-)
Simon

[Ken Puls]

Well, we have VBA skills here.

The issue I'm seeing though, is that I don't think that there is a way to intercept the updating after the workbook is opened. If you were to build an add-in, and the users were instructed to only open items via those menus, then you could check before the workbook was even opened. At that point you could tell them not to.

The only other way I can see around this is potentially by forcing all of your files into manual mode. I'm not a big fan of that idea though, but it may be a good idea if you're trying to audit/compare. The rub is that, if you forget you're in manual mode then your new workbooks inherit it too...