Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    -> 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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •