I wish I had learned this years ago.
May. 29th, 2012 03:44 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
plonqSomething 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.
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.
