[RESOLVED] VB6: Excel won't close after sorting

STARTED NEW THREAD************************

I am having an issue where Excel doesn't close when it should. Here is a very simplified version of my code. If I remove the code identified as "Problem Area" then Excel will close as expected at the end of this procedure. If the problem area code is included then Excel will not close. Somehow it seems to be related to the Range still being active which keeps Excel open. Any ideas?

Code:

`Dim oExcel As Excel.Application`

Dim oWbk As Excel.Workbook

Dim oSht As Excel.Worksheet

Dim oRng As Excel.Range

Set oExcel = New Excel.Application

uploadpath = "C:\Excel File.xls"

Set oWbk = oExcel.Workbooks.Open(uploadpath)

Set oRng = oWbk.Worksheets(1).Range("A8").CurrentRegion

K1Rng = "A8"

K2Rng = "E8"

'*******************************PROBLEM AREA

Range("A8:AC100").Select

Selection.Sort Key1:=Range(K1Rng), Order1:=xlAscending, Key2:=Range(K2Rng) _

, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _

False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _

:=xlSortNormal

'*******************************END OF PROBLEM AREA

Set oRng = Nothing

Set oSht = Nothing

oWbk.Close SaveChanges:=False

Set oWbk = Nothing

oExcel.Quit

Set oExcel = Nothing

Note: This code works. It opens Excel, loads the spreadsheet, performs the correct sorting on columns A and E. The only thing it doesnt do is close Excel when it is done. If the lines marked above as "Problem Area" are removed, then Excel does close properly. Something in those lines keeps Excel open.

Re: VB6: Excel won't close after sorting

Good News! I solved my own problem(s). Basically had to make sure that my Range and Selection statements had the appropriate parent objects specified. Corrected code is shown below:

Dim oExcel As Excel.Application

Dim oWbk As Excel.Workbook

Dim oSht As Excel.Worksheet

Dim oRng As Excel.Range

Set oExcel = New Excel.Application

uploadpath = "C:\Excel File.xls"

Set oWbk = oExcel.Workbooks.Open(uploadpath)

Set oSht = oWbk.Worksheets(1) '<---- this was added also

Set oRng = oWbk.Worksheets(1).Range("A8").CurrentRegion

K1Rng = "A8"

K2Rng = "E8"

'*******************************PROBLEM AREA

oSht.Range("A8:AC100").Select

oExcel.Selection.Sort Key1:=oSht.Range(K1Rng), Order1:=xlAscending, Key2:=oSht.Range(K2Rng) _

, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _

False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _

:=xlSortNormal

'*******************************END OF PROBLEM AREA

Set oRng = Nothing

Set oSht = Nothing

oWbk.Close SaveChanges:=False

Set oWbk = Nothing

oExcel.Quit

Set oExcel = Nothing

Re: [RESOLVED] VB6: Excel won't close after sorting

Thanks for posting your solution, this was the only post on the internet that helped me solve it!

Re: VB6: Excel won't close after sorting

Well this was an old post, but nevertheless, there are still alot of us using VB6, due to the cost of reprogramming 10 000 code lines in a system still working fine, both on Win 7 and Win 8, although written back in 2006.

I thought my Excel code worked fine, until someone demanded a sort option being included. That's when my headaches begun. I discovered exactly the same problem that you did.

But your efforts saved the day! Cannot thank you enough!