The stupid Excel workbook won't go away.

[ChrisCote]

I am having what seems to be a very simple problem, but I can't get it working. I finally figured out how to create an Excel 7.0 file in VB 6. Now the only problem I have is that after I create the file, save it and quit the file, it doesn't go away. So when I try to access it later, I can't because it's already open. What do I do?

[Castor]

Could you please post the code you're using to create the workbook? It could be that you're not destroying the object variables before closing, or even that you're not destroying them in the right order. A code sample would help a lot.

thanks

[ChrisCote]

OK, here is some of the code.
On Error GoTo ErrHandler
If msDBType = qsEXCEL_7_0 Then
Set MyExcel = New Excel.Application
MyExcel.Workbooks.Open msDBName
For i = 1 To MyExcel.Worksheets.Count
If UCase$(MyExcel.Worksheets(i).Name) = UCase$(msDBTable) Then
Exit For
End If
Next i

With MyExcel.Worksheets(i)
For j = 1 To mnNumDims + mnNumNLabels + mnNumTLabels + mnNumULabels
sFieldName = Trim$(MyExcel.Worksheets(i).Cells(1, j))
lstDbLabels.AddItem UCase$(sFieldName)
lstDbLabels.ListIndex = j - 1
sDBString = sDBString & "," & sFieldName
nDBCount = nDBCount + 1
DoEvents
Next j
End With
MyExcel.ActiveWorkbook.Close False
MyExcel.Quit
else
'Some other code
end if

This is in the load event of one of my forms.

[ChrisCote]

Sorry, I forgot to tell you that this form is called from another form.

[ChrisCote]

Sorry, I forgot to tell you that this form is called from another form.

[ChrisCote]

Sorry, I forgot to tell you that this form is called from another form.

[AaronC]

I think you want a 'Set MyExcel=Nothing' in there after you quit excel.

[ChrisCote]

I actually thought of that after I wrote that message, but that also didn't work.

[bashfirst]

There are a number of Knowledge Base articles about the variety of ways Excel automation can get screwed up as far as releasing an object reference or not. I had a similar problem where the user could close Excel but it will still appear in the Task list. The way I solved it was to use the
GetObject method to open my Excel object. If that fails (or if you always want to open a new Excel session) then use CreateObject instead of New Excel.Application. I have no idea why this should make a difference, but it worked for me. Still, I recommend you check out the Knowledge Base.

Hope this helps.
Bash

[Lloyd]

ChrisCole,

Have you tried the following 2 lines

MyExcel.Application.Quit
Set MyExcel = Nothing

Hope this helps some...

[ChrisCote]

Nothing has worked yet!! I can't find anything on this problem. I tried the CreateObject and OpenObject, but I got an error message saying something about ActiveX not being able to open or create and object.

[LordCallubonn]

Did you add a reference to excel? Just wondering..

[ChrisCote]

Yes I did. I have everything else working. If I try to close the Workbook, Excel brings up a message saying the file exists, would you like to overwrite. If I just click the Yes button, it's fine, but this program is supposed to have almost no interaction with the user.
As for the Excel not going away, when I do the Excel.quit and set excel = nothing, it doesn't get rid of one of the Excels.

[JHausmann]

Use the following in your Excel close event (you send):
SaveChanges:=False

For example (MyExcel defined as an object):

MyExcel.Close SaveChanges:=False

Then, reopen the workbook. I'd use this to solve my post (in the general forum) except I get the stinkin virus warning message every time (and, yes, I know how to turn it off but can't/won't).

[LordCallubonn]

this is the code I use to export to a excel file..

Private Sub mnuexport_Click()

Dim d As Database
Dim r As Recordset
Dim q As QueryTable
Set d = OpenDatabase(App.Path & "\Peopledb.mdb")
Set r = d.OpenRecordset("SELECT * FROM Info")

Dim x As New Excel.Application
x.Application.DisplayAlerts = False
x.Visible = False

Dim w As Worksheet
x.Workbooks.Add
Set w = x.Worksheets(1)
Set q = w.QueryTables.Add(r, w.Range("A1"))
q.Refresh (True)

x.Workbooks.Application.SaveWorkspace "c:\Personal.xls"
MsgBox "Saved Production to EXCEL97.", vbInformation, "Personal.xls Saved!!"
'x.SaveChanges = False

x.Quit
Set d = Nothing
Set r = Nothing
On Error GoTo exit2

exit2:
Exit Sub
MsgBox "WE GOOFED UP!!!!", vbInformation, "Personal.xls Not-Saved!!"
End Sub

When the user hits cancel ont he saveas screen , it comes up with a 1004 error, how can i trap this litte pain??

[LordCallubonn]

You are welcome. But my 1004 error is still buggin me, can you happen to find out how to trap it..? When the saveas thing comes up & I hit cancel it says object not created 1004 error..

------------------
~\*/~ Thank you....

[JHausmann]

Well, I don't use the construct

Dim x As New Excel.Application

So i don't know if VB can trap the error but I'd try:


if Err.Number = 1004 then
err.clear
exit sub
end if

'put the above on the line following : x.Workbooks.Application.SaveWorkspace "c:\Personal.xls"



[This message has been edited by JHausmann (edited 08-09-1999).]

[LordCallubonn]

I tried it above & below & both.. No go.. Guess this is a untrappable error.. Fart..

------------------
~\*/~ Thank you....

[ChrisCote]

Finally, it WORKS!!! Thank you so much!!