Excel won't go away after using Selection

Hello,

I've got a problem with Excel which is really winding me up.
I've worked through quite a few similar problem reports in the forums but to no avail ...

The problem is that the following piece of code will leave an excel process running until the application is closed (i.e. sub main terminates, trace through the code to verify):

Sub main()

Dim xl As Excel.Application
Dim wb As Excel.Workbook

Set xl = CreateObject("Excel.Application")
xl.DisplayAlerts = False

Set wb = xl.Workbooks.Open("c:\test.xls")

'The following line causes the problem
Selection.Clear

Set wb = Nothing

xl.Quit
Set xl = Nothing

End Sub

Any reference to the Selection property of the Excel Application object will result in Excel not terminating after xl is set to Nothing. Obviously I could refrain from using Selection, but what if I can't do without? Any ideas?

Cheers,

Nick.

P.S. I'm using VB 5.0 with Excel 97 on Windows NT 4.0.

[WadeD]

I'm not a VBA guy, but if Excel generates some sort of error from your Selection.Clear line, it may refrain from any further processing in the procedure. Maybe perform a simple task right after Selection.Clear to verify that an error's not terminating your code?

[HDR]

I think you needed to be more explicit in your instructions. Apply the changes below and it works fine.

Dim xl As Excel.Application
Dim wb As Excel.Workbook

Set xl = CreateObject("Excel.Application")
xl.DisplayAlerts = False

Set wb = xl.Workbooks.Open("c:\test.xls")

'The following line causes the problem
' Selection.Clear
xl.Selection.Clear

'Set wb = Nothing

'xl.Quit

xl.Workbooks.Close
xl.Application.Quit

Set xl = Nothing

WadeD: No trappable error is generated, nor do any other operations in Excel fail, everything just works fine except for shutting Excel down.

HDR: Replacing Selection.Clear with xl.Selection.Clear makes all the difference! Excellent! Thanks a million

Nitro: Worksheets don't have a Selection property?

[Edited by groovy_nick on 05-03-2000 at 05:12 AM]

[sintjoost9]

I have the same problem and read almost al the posts about it (lots of people with the same problem). I think it is stange that somany people have this problem, but I've not yet read a 100% solution.
Your problem seems to be the most simular to mine, because it also occursin a Selection statement.

Here is a part of my code:

Public Sub Test()
Dim wBook1 As Workbook
xls_file = frmMaakpro.txtFile.Text
Dim appExcel1 As Application
Set appExcel1 = CreateObject("Excel.Application")
Set wBook1 = appExcel1.Workbooks.Open(xls_file)
appExcel1.Visible = True
wBook1.ActiveSheet.Range(wBook1.ActiveSheet.Cells(3, 1), wBook1.ActiveSheet.Cells(10, 3)).Select

'Next line the error will occur
appExcel1.Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'--ending EXCEL
wBook1.Save
wBook1.Close
appExcel1.Workbooks.Close
appExcel1.Quit
Set appExcel1 = Nothing
Set wBook1 = Nothing
End Sub

I think I closed everything, and the first time I run this code, everything works fine. But the second time(without exiting my program) my program crashes on the Selection.Sort line : error 1004.
I think the error occurs because Excel is still active (Task Manager), but I cannot seem to Quit it.
When I close my project, excel also closes

Hope you could maybe try this code and maybe add or delete a few lines, so it will work well.

(Maybe I have to 'unselect' my Selection???, but how)

Thanks in advance

John