Excel Application won't quit

[Miked]

hello all!

I am asking a somewhat familiar question, but the reponses I've seen on
the group don't quite answer the question. Like Most, I'm piping
through to an excel applicatio to handle some data. here are my
opening lines:

Set xlApp = CreateObject("excel.application")
xlApp.Visible = False
xlApp.DisplayAlerts = False

Set xlWB = xlApp.Workbooks.Add
Set xlWS = xlWB.Worksheets(1)
xlWB.SaveAs (filename)
Set db = OpenDatabase(filename, False, False, "Excel 8.0")
Set rst1 = db.OpenRecordset("datasheet")

Then I do some processing. I want to close the excel WB, WS and db, as
well as the application, so I invoke what I thought to be pretty
standard code:

'delete the existing file
If IsNothing(xlWB) = False Then
xlWB.Close False
Set xlWB = Nothing
End If

If IsNothing(db) = False Then
db.Close
Set db = Nothing
End If

If IsNothing(xlApp) = False Then
xlApp.Application.Quit
'xlApp.Application.Close
Set xlApp = Nothing
End If

'kill the recordset as well
If IsNothing(rst1) = False Then
rst1.Close
Set rst1 = Nothing
End If

'check to see if the file exists first
filename = "c:\FSDdata.xls"
check = Dir(filename)
If Len(check) > 0 Then
VBA.Kill (filename)
End If

This seems to work fine, but on the next go around, when I try and get
the same code, I get a permissions error on "filename". I look in the
task manager, and excel is still running in the background. How come
the lines above don't work? For the xlApp, I've tried:

xlApp.Close
xlApp.application.close
xlapp.quit

And nothings works! Again, the goal is to get the app that is working
in the background to be gone, so that I don't get a permissions
violation when accessing the same file.

Thanks so much in advance for your help!

Mike D

 

[Tony Jollans]

What is IsNothing? It's not part of Word VBA. Try using ...

If Not xlApp Is Nothing Then .....

and similarly for the other conditions.

 

[Miked]

Tony:

Thank you for your reply. IsNothing is a function I created to read
whether or not an object is set to nothing, and returns true or false
depending. Here it is:

Function IsNothing(X As Variant) As Boolean
IsNothing = X Is Nothing
End Function

I hope this answers your questions. Either way, I'm still unable to
shut-down the excel process.

Thanks again!

Mike

 

[Tony Jollans]

Hi Mike,

OK - that seems good. Needs some guesswork on my part now.

1. Is there a possibility you somehow implicitly instantiate an object which
holds on to Excel anywhere in your code - some not-fully qualified reference
somewhere?

2. I'm not sure what the relationship of the execl objects is to the
database objects but I would think it better to close and destroy the
recordset before the database - and possibly both before the workbook. As a
general rule destroy in reverse order to creation.

Beyond that I can't see anything else.

 

[Miked]

Ok, so I've given up on trying to shut down the excel process. Where I
am now is that I have put a new configuration in place that seems to be
handling things better. Here's my attempt to explain things. I think
I might need some conceptual education, becuase it's still not working
even when I think it should.

Here's what's happening in sequence
1. create excel application
2. add a workbook
3. delete the file with the same name, if exists
4. create a worksheet inside that workbook
5. Name each of the first four columns
6. create & set a named range equal to first four columns, first row
only
7. save workbook
8. create a DAO database (set db=OpenDatabase(filename, False, False,
"Excel 8.0"))etc.
9. set a recordset equal to the named range (Set rst1 =
db.OpenRecordset("FSDdata"))

So that gets me going, no problem.

Now, i need to update the recordset, but I don't know how many records
have changed, so I want to kill it all and reset it. Here's what I'm
doing & observing:

1. close recordset
2. set recordset = nothing
3. create a worksheet inside existing workbook
4. Name each of the first four columns
5. create & set a named range equal to first four columns, first row
only
6. save workbook
7. set a recordset equal to the named range (Set rst1 =
db.OpenRecordset("FSDdata"))
8. insert values into recordset through following code:

With rst1
.AddNew
.Fields("Index").Value = i
.Fields("category").Value = ctemp
.Fields("type").Value = ttemp
.Fields("view").Value = vtemp
.Update
end with

When I get to the .Update, I get an error that states:

"Cannot expand named range. err#3434

Any ideas? Clearly, there is something I missing becuase there is
still a link between the named range and the previous workbook. But
what am I missing in order to clear it out.

BTW, using this means that I don't have to close the excel application,
but I keep it running.

Thanks in advance again!

Mike

 

[Tony Jollans]

Sorry for not getting back sooner. I'm afraid this slipped through the net -
mostly because I don't know much about using recordsets like that in Excel.

I have had a bit of a play with it and it seems to me that there may be some
sort of conflict between having the workbook open in Excel and as a
database. Do you need to have both at the same time? What about closing the
workbook - and excel - before opening the database and recordset.

I think if you continue to get stuck you would be better asking in one of
the Excel newsgroups - there's much more likely to be someone there who
knows.