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