invisible open instances of excel

chazw

New Member
Joined
May 24, 2011
Messages
26
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:

Code:
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):

Code:
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:

Code:
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:

Code:
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top