invisible open instances of excel

[chazw]

Hey all:

I'm exporting an access table to workbook. The access DoCmd.TransferSpreadsheet creates the workbook, invisibly.

I check for open instances after this as follows:

debug.print Excel.Workbooks.Count

and the result is invariably zero.

Then I open the workbooks, format them, and close them with this bit (surely you recognize it, you probably wrote it):

Dim xlApp as Excel.Application
Dim xlBook as Excel.Workbook
Dim xlSheet as Excel.Worksheet
 
set xlApp = CreateObject("excel.application")
set xlBook = xlApp.Workbooks.Open("workbookName")
 
For each xlSheet in xlBook.Worksheets
 
apply the format
 
Next
 
xlBook.Close (True)
xlApp.Quit
 
Set xlBook = Nothing
set xlApp = Nothing

OK, no problem. But today I decided to tackle the problem of some of the resulting numbers in the exported sheets being text, and so I found (right here on this site) a way to convert them by range to numbers, with the following:

sub nfmt()
 
with activesheet.used range
   .value = evaluate(.address & "*1")
end with
 
end sub

Now of course a call to this sub occurs before the "NEXT" statement in the format loop, so that it can do its thing on each xlSheet.

Only now, when all is said and done, I go to check on the output, and I get "'myWorkbookName' is already open" warnings.

If I try delete the exported books, I get errors stating that I can't delete them, because they are open.

So, I try to find out how many invisible wkbooks are open -- because there is no visible wkbooks open -- with this:

dim objXL
set objXL = getObject(,"Excel.Application")
 
Do until TypeName(objXL) = "Empty"
 
debug.print Excel.Workbooks.Count
 
objXL.Quit
set objXL=Nothing
 
Loop

The debug.print returns zero, sometimes. I cannot see a pattern to that. But I still get the open workbooks warnings regardless.

This loop will apparently run forever if I let it. But I don't. Eventually I break it, and go check on the exported workbooks, get the errors, run the loop again for a while, and it closes 1 workbook. Then I do this again (and again) until all are closed I can delete them all.

I don't know really what's going on here,would love some insight if anyone has any.

Note that when I remove the call to nfmt() and settle for the text/numbers problem, there don't seem to be any phantom open workbooks. Can't imagine what the sub nfmt() has to do with that...

???

Thanks in advance.

 

[chazw]

and son-of-a-gun if I didn't just come across a big clue here: http://www.btabdevelopment.com/ts/excelinstance

 

[chazw]

As a bit of an addendum to this, here's what I discovered:

The link posted above is a tremendous help. Bottom line, you have to be EXPLICIT about the objects you reference when you do it from Access.

Interestingly, if, say when debugging, something causes multiple invisible opens from Access, I can find and kill one at a time with:

Dim objXL
 
set objXL = getobject(,"Excel.Application")
objXL.activate
activeworkbook.close

However, I cannot loop this procedure and find n instances. I have to stop the exection of this sub and restart it (f5) to find another instance.

a tad confusing to a hack like me.