Selected Answer
You won my heart, Cathy, because I hate links, too. As for your question, I wonder if you would bear with me while I guess.
Sure, a link is a link but, as you have found out, not all files were created equal. Excel does treat files it considers unsafe differently, to wit, it places them in some sort of quarantine until you press "Enable "Edits". "Qurantine" may not be a bad word to use because such files aren't really opened when they are opened. Instead they are just displayed but their content, in particular code, kept separate from anything in the computer's memory.
Now, I think it stands to reason that a reference to a file which isn't really open, just displayed, and not connected to anything in the computer, shouldn't work. Hence the #REF! error.
You say, the #REF! error happens when the file is opened "normally". In my theory that would be an overstatement. Excel doesn't open workbooks it suspects normally. It opens them in their own special way. So, the first hope would be that the links can be restored after clicking "Enable Edits". Quite likely, that button doesn't come up in your circumstances. But if the underlying cause is the one I suspect then the button itself wouldn't matter. We could presume that Excel first acted on its suspicion, then changed its mind after a second look. Accordingly, clicking on "Refresh Links" should establish the reference links. (I'm not so sure of the precise name or location of the button - compare rant above - but I know it exists.)
"Should" is the key word because I think it won't. My theory would have Excel open the file in a separate instance. So, yes, the file is finally open but not in the same instance as the rest of your Excel. So they can't "see" each other. You can test this either using VBA and counting the number of instances of the Excel.Application currently running or by quitting the Excel application of either workbook and see if both workbooks close.
If my theory is correct there is nothing you can do after the event. That leaves room for action only before. Usually, Excel will mark a workbook as unsuspicious after it has been approved ("Enable Edits" was clicked) and saved. "Normal" opening should be possible thereafter. However, you may still need a better definition of "normal". I have a lot of external files on my PC and files which used to be external and have been saved. Therefore I suspect that my observations may not be universally true. With that said, I observe that Excel often creates new instances of itself unexpectedly. I think when I open a workbook by double-clicking in Windows Explorer I get a new instance of Excel at least most of the time, while File > Open will open the file in the instance that I am calling the command from as is applicable to files opened by VBA.
