There is no consistent rule for when it decides to fail, with the possible exception that it appears to fail more regularly on databases that are stored locally, rather than ones stored on a network share. I have researched the problem now and again, sifting through on-line help forums, but I found a fairly common theme.
1) Somebody describes a problem almost identical to what I have been experiencing. "The Excel query appears to be corrupting the table names on some of my databases."
2) A user offers up a solution that appears to have no bearing on the problem. "Maybe you need to eat more cheese."
3) First user accepts that solution. "Worked great! Thanks!!!"
I ran into the problem again today, and once again I decided to wade into the heartbreak of forum inanity to see if anyone actually had a useful solution to offer. I was about to give up again in disgust when I noticed an off-hand comment in a thread of otherwise useless advice:
I just went into Excel and selected Help-->Detect and Repair and the problem went away.
Well... shit. It worked like a damn. For some reason I thought the solution would be much more complex than that.