Excel 2007 Error: Link source not found

Another hard to reproduce one – but I think we may have actually solved this one. (after a fair amount of pain it has to be said)

Open a new Excel 2007

Open 2 wbs and save them as .xlsms.

In one put some text in one cell

in the other link to that cell.

Save and close both. (perhaps in a trusted location, and or set all external connection security to the minimum)

Open the source.

Then open the one with the link in, bear in mind this is now a link to an open workbook. That should just silently update. But no… Instead on several machines for several users we get this:

cantupdt

Click Edit links to get to here:

linksourcenf

Source not found? SOURCE NOT FOUND?? Its behind you you ejit!

Click close on here or continue on the previous one and it just updates the link. Next time they open the file the same problem occurs. Its like at the moment of open Excel just cannot understand the link, and gives up. I think its the same across all file formats, but we didn’t test fully. It occured for every file with an external link in.

I tried all the usual Excel based things to try and sort it but nothing worked. One of the sys support guys suggested it might be corrupt profiles. And sure enough, if those affected create a new profile by logging into a new machine then the problem goes away.

Dunno how or why, but if it happens to you hopefully you won’t waste as much time as we have before trying this.

If you have seen this issue then plese let us know how you fixed it.

Have a good weekend.

cheers

simon

Advertisements

13 Responses to “Excel 2007 Error: Link source not found”

  1. Harlan Grove Says:

    I tried to reproduce this in Excel 2003.

    – launch a new Excel session,
    – create 2 blank wbs,
    – enter foobar in Sheet1!A1 in Book2,
    – Enter =[Book2]Sheet1!A1 in Sheet1!A1 in Book1,
    – save Book1

    At this point Excel displays a dialog asking whether I want to save Book1 with references to unsaved documents. Does that happen in Excel 2007 if you try saving the wb with the ext ref before saving the source wb?

    Anyway, if I do save Book1 before saving Book2, then close Book1, then save Book2, then reopen Book1, I get the same behavior you describe: Excel first shows a dialog saying the file contains links to other sources – click Update, then Excel shows the dialog saying the links can’t be updated. So this doesn’t seem to be a change in Excel’s functionality as long as you save Book1 with references to as yet unsaved Book2.

    Note: if I save Book2 as Book2 rather than as Book2.xls (it take some work to do so – you have to enter the filename as “Book2.” including the double quotes AND the ending period), then I reopen Book1.xls, it opens as expected, automatically updating the link to [Book2]Sheet1!A1.

    From my perspective Excel is making a proper distinction between one file named Book2 and another named Book2.xls. That’s why a saved reference to Book2 rather than to Book2.xls in Book1.xls is properly treated as a problem if there’s a Book2.xls but not a Book2.

    To me the question is whether Excel 2007 displays the warning message about saving external references into unsaved files (Book2) if you try to save Book1 first. Does it? If not, that’s a very nasty bug in Excel 2007.

  2. Simon Says:

    Harlan
    Yes it does warn of links to unsaved wbs. I should have been clearer on the order.

    If you save the source then the link, close Excel then open the source, then change whatever the setting is in 2003 to update without a prompt. Then open the wb with a link it should just find the already open source. No prompts, no ‘cannot update’ warning.

    On a handful of machines 2007 doesn’t. I never saw this in 2003, but it could be a problem there too.

  3. Harlan Grove Says:

    Workbooks named Book2 and Book2.xls are different files. If you save an ext ref to Book2 before it has been saved, then the ext ref is to a file named Book2, not a file named Book2.xls. If you close Book1, save the source wb as Book2 (by entering “Book2.” in the filename field when saving it the first time), then either leave it open or close and reopen it then reopen Book1, there’s no problem. Excel 2003 can evaluate =[Book2]Sheet1!A1 because there’s an open file with base filename Book2.

    However, if you save the source wb as Book2.xls, then when you reopen the other wb, Excel (in my opinion) correctly complains about not being able to find the linked wb named Book2.

    This all comes down to how you or I answer the question ‘Should Excel treat BASENAME and BASENAME.XLS as the name of the same file?’ You seem to believe it should, I believe it shouldn’t. Call this a picky syntactic quirk, but filename extensions matter to Excel.

    You’re not clear about precisely how you’re saving the source wb. Are you saving it by entering “Book2.” (or whatever) in the filename field or pressing Enter/clicking the OK button? If the latter, you’re saving the source wb as Book2.xls rather than as Book2. That makes all the difference, at least for Excel 2003.

  4. Simon Says:

    Harlan They were saved as book1.xlsm and book2.xlsm
    I thought the edit links showed the full path (or at least the name +.xls) on wbs that are closed, but just the ‘friendly name’ on wbs that are open. On checking I see what you mean in 2003 it should say book2.xls, not just book2.

    That might be a quirk of the screen shots used. We tried for any number of files and anything with a link initially could not update, and that is the issue. When you click close on the 2nd dialog it updates fine.

  5. GeoLS Says:

    It ironic I am having this same problem today. I am a little confused on the solution to this problem. What do you mean by create a new profile?

  6. Stan Scott Says:

    I had this same error about a month ago. Here’s what did the trick for me.

    For some unknown reason, there was a hidden range name that was a reference to another workbook.

    I made all the ranges visible with a simple FOR loop:

    For Each n In ActiveWorkbook.Names
    n.Visible = True
    Next

    then listed them out and there it was.

    This may not be the problem you’re having, but I thought I’d add this, in case anybody could use it.

    Stan

    • Peg Molter Says:

      Stan,

      You just saved me additional painful hours of frustrating work. Thanks so much for the range name hint.

      Peg Molter
      FTC&H

    • Omar Freeman Says:

      This blast from the past just solved a problem I was having with a set of files in Excel 2010 and 2013. In making wholesale changes using 2013 to a file that started life in 2010, somehow I ended up with a bunch of hidden range names. They don’t make sense to me so I think the copy of 2013 I’m using has something going on in it.

      Anyway, a few seconds to run this macro finally solved my problem. It’s now in my Personal macro toolbox, with credit back to this page, of course!

  7. Ravi Says:

    Hi Simon,

    the reason the links give a #REF! error is because Excel recalculates the work book.entire workbook and also forces to update external links.The following Microsoft Article talks about the workaround/Cause of teh issue :
    http://support.microsoft.com/kb/925893

  8. mhel bergola Says:

    why is it when im going to save on excel, the filename comes with .xls (filaname.xls) though i dont input the “.xls” extention on it. hope u can help me resolving this issue. tnx

  9. Ravi Says:

    Hi,

    you will see the file extension when you save files if you have unchecked the option ” hide file extensions of known flile types” in the folder options for windows. Check this option and you should not be seeing the extension of file types when you save them or view them on windows explorer

    thanks’
    Ravi

  10. Joe Says:

    I saw this error today. Apparently after you change or update the source, you have to click on update values, otherwise the changes you made don’t stick.

  11. Imran Says:

    Source not found – Solution
    Hi,

    Please check the option – (Ask to update automatic links) going to – Tools – Options – Edit and this should resolve the problem. Please let me know the status. Thank you.

    Imran

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: