Can't close an open excel application from VB

[fred43200]

I'm having problems closing my excel application after using range and sort.
Here is a snippet of my code:

Set xApp = New Excel.Application
Set xBook = xApp.Workbooks.Open(filename)
Set xSheet = xBook.ActiveSheet

xSheet.Range("A12:I300).Sort(Columns("I"))

xBook.SaveAs OutPutFilename

xBook.Close True, OutPutFilename

xApp.quit
Set xSheet = Nothing
Set xBook = Nothing
Set xApp = Nothing

After this code executes I can open task manager and see that Excel is still open. Any help would be greatly appreciated. Thanks!

[dglienna]

Does it finish up after a few seconds? You are telling it to sort and save the file. Does it? Or is it still running 10 minutes later, after closing the app?

You must be calling it somewhere else in your program. If those 6 lines alone don't work, it's a timing thing

[fred43200]

It sorts and saves the excel file, but doesn't close the excel application.

[dglienna]

Try this. It closes properly.

Option Explicit

Public xlAppTemp As Excel.Application
Public xlWorkBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Dim strDate$

Public Sub GenerateReport()
  On Error GoTo ErrHandler
  
  ' Creating Object for Excel File.....
  Set xlAppTemp = New Excel.Application
  
  ' Making it Invisible and non-Interactive.....
  xlAppTemp.Visible = False
  xlAppTemp.DisplayAlerts = False
    ' Opening Template Excel File.....
  Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
  Set xlSheet = xlWorkBook.Sheets(1)

  ' Making Active to Worksheet 1.....
  xlSheet.Activate

  ' I am doing lot of things in it, but to provide you with example
  xlSheet.Cells(15, 1) = "This is my report 1"

  ' Formating Date to attach with new file name.....
  strDate = Format(Date, "yyyy-mm-dd")
  
  ' Saving excel file with new name on different folder.....
  xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"

Cleanup:
  ' Destroying Objects.....
  Set xlSheet = Nothing
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing
'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True
  xlAppTemp.Quit
  Set xlAppTemp = Nothing
  Exit Sub
ErrHandler:
'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
'(save happens just above if no error occurs)
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing

'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True

  xlAppTemp.Quit
  Set xlAppTemp = Nothing
End Sub

Private Sub Command1_Click()
  Call GenerateReport
  Beep
End Sub

[fred43200]

Thanks for your input. My problem is the line of code where i sort:
xSheet.Range("A12:I300).Sort(Columns("I"))
If I remove that line it closes every time. I used the code you gave me and it won't close.

[dglienna]

Did you try putting a SLEEP() in? Or did you say it never finishes?

[fred43200]

It completes the sort. I can open the excel file and see the results, but if I open task manager the excel file is still open and I have to manually end the process.

[dglienna]

Try this. I can see Excel in Task Manager when I click the button, then disappears. It doesn't stay running, even while the vb.app is running!

[fred43200]

Your code works fine, but if I add this line of code Excel won't close:
xSheet.Range("A1:B1").Sort(Columns("A"))

[twboiid]

Did anyone ever post a working reply to this problem?? I am having the same issue that Fred describes. 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?


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

'*******************************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

[dglienna]

Does your second key have a value?

Key2:=Range(K2Rng)

Did you record that using a MACRO? Does it run in Excel?

Usually it's best to start a new thread, rather than adding to one that's OLD

We assume the poster forgot to mark the thread as RESOLVED

I think it was, as he rated me for my assistance...

[twboiid]

Yes, K2Rng does have a value. I started a new thread as you suggested. I also added the missing lines where K1Rng and K2Rng are defined.