and things went downhill from there

Something that I do a fair bit of on this job is combining data from various sources in Access and then pushing it out to Excel and publishing it from there. Normally it works very well, but there is a vexing issue that has caused me a lot of grief over the years.

Sometimes, with no apparent motive that I could find, Excel would remain active in the background, even after I sent it Close and Quit commands. The only way to get rid of it would be to go to the task manager (or Process Explorer in my case) and manually kill it. In some cases this idling version would interfere with other jobs I was trying to run, and other times it would not. This time it did - usually. Either way, it then stacked another idle version of Excel.exe in the background because I obviously needed more ghost processes running.

I finally got pissed about it enough today that I did some research thinking that I could not be the only person encountering this frustrating feature, and I found plenty of hits. Good. More hits increased the odds that I would find a useful answer buried in the usual forum stupid. By "usual forum stupid" I mean answer like these:

"I know you were asking about VBA, but here is the solution in C#"
"I can't reproduce the error."
"Have you tried [something he has obviously tried because it's in the source code he provided]?"
"Have you tried X?" "OK, I tried X and it did not work." "How about X. Did you try that?" "That's the same fucking thing." "I know, but it works for me."

Anyway, after wading fruitlessly through the usual sea of unhelpful responses, was skimming through answers so quickly that I almost missed the one I needed. About three years ago somebody was having the same problem in Excel 2007, and one of the guys in the forum offered up what he thought seemed like a reasonable answer.

"My hunch is that you that you have something that is not tied to an instantiated object. You need to tie everything to the object otherwise Access instantiates another instance of Excel which it then uses for those non explicitly referenced items."

I am pretty good about that, but I poured through my code a few times and then spotted it:

.xlSheet.Range(Cells(iRow,iCol1),Cells(iRow,iCol2).Merge

I fixed it, and the problem went away. I have some other reports that I need to fix (so that I can get rid of the job I created whose sole purpose is to go through every evening and nuke all of these lingering instances). Now if you will excuse me I need to go cut myself.

I have experienced this random, recurring glitch between Excel and Access that has been driving me up the wall for months. A lot of what I do involves using MS Access to process data from various sources, which I then (try to) link to data sheets in Excel for the final processing steps. Nine times out of ten the process works flawlessly, but sometimes it will randomly decide that it is not going to work with a particular database or table.

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.