Solved: Closing Excel Application Problem

Anybody,

How can I close an excel application. I tried using .QUIT function, it closes the application but for some reason I can still see it under Task Manager/Processes and part of my code is failing because of this.

Thanks in advance.

:dunno

You sure you didn't have other instances of Excel open? Perhaps you had created them in your code but got an error that prevented .quit from executing? On good rule of thumb is when automating other apps in office, always use an error handler that cleans up by either making them visible or quiting things that are open.

Hmm, a similar problem here.. http://www.theofficeexperts.com/foru...ead.php?t=4690

I was just executing this code, the middle part was removed. Kindly verify if there is something wrong with it.

[vba]Sub NineHundredMHzImport()

Dim ex2 As Excel.Application
Dim wb2 As Excel.Workbook
Dim db As DAO.Database
Dim WZVoice As DAO.TableDef
Dim WZColor As DAO.TableDef
Dim WZCellID As DAO.TableDef
Dim WZLatLong As DAO.TableDef
Dim WZSpecific As DAO.TableDef
Dim SiteName, SectorNo, TechType As String
Dim CarIndex As Integer
Dim LatDeg, LatMin, LatSec, LongDeg, LongMin, LongSec, sectorcount, LatDec, LongDec As Double
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select a 900 MHz CellList file to import...", _
Flags:=ahtOFN_HIDEREADONLY)

'Exit if dialog box is cancelled
If strInputFileName = "" Then
MsgBox "No file was selected."
Exit Sub
End If

' Turn error checking off
On Error Resume Next
Set ex2 = New Excel.Application
Set wb2 = ex2.Workbooks.Open(strInputFileName, False)

' If an error occurs report it and end
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
Exit Sub
End If

' Turn error checking on
On Error GoTo 0
Set db = CurrentDb
Set rst3 = db.OpenRecordset("SELECT * FROM WZVoice")
Set rst4 = db.OpenRecordset("SELECT * FROM WZColor")

'Show Excel Application
ex2.Visible = True

wb2.Close SaveChanges:=False
ex2.Quit

Set wb2 = nothing
Set ex2 = nothing

End Sub()[/vba]

Would you edit that and enclose the code in vba tags?

Here

[VBA]

Sub NineHundredMHzImport()
Dim ex2 As Excel.Application
Dim wb2 As Excel.Workbook
Dim db As DAO.Database
Dim WZVoice As DAO.TableDef
Dim WZColor As DAO.TableDef
Dim WZCellID As DAO.TableDef
Dim WZLatLong As DAO.TableDef
Dim WZSpecific As DAO.TableDef
Dim SiteName, SectorNo, TechType As String
Dim CarIndex As Integer
Dim LatDeg, LatMin, LatSec, LongDeg, LongMin, LongSec, sectorcount, LatDec, LongDec As Double
Dim strFilter As String
Dim strInputFileName As String
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select a 900 MHz CellList file to import...", _
Flags:=ahtOFN_HIDEREADONLY)
'Exit if dialog box is cancelled
If strInputFileName = "" Then
MsgBox "No file was selected."
Exit Sub
End If
' Turn error checking off
On Error Resume Next
Set ex2 = New Excel.Application
Set wb2 = ex2.Workbooks.Open(strInputFileName, False)
' If an error occurs report it and end
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
Exit Sub
End If
' Turn error checking on
On Error GoTo 0

sectorcount = 0
Set db = CurrentDb
Set rst3 = db.OpenRecordset("SELECT * FROM WZVoice")
Set rst4 = db.OpenRecordset("SELECT * FROM WZColor")
'Show Excel Application
ex2.Visible = True
wb2.Close SaveChanges:=False
ex2.Quit

Set wb2 = Nothing
Set ex2 = Nothing

End Sub


[/VBA]

uffda! wonder what happened there

I editted the previous one I send. Thx

If you make it to the end of your procedure everytime, then it'll quit Excel. This problem often results from debugging. I experience it a lot when I'm not paying attention. If the code it stopped for any reason before it has a chance to make it to the end of the procedure, then Excel won't quit. I'm stating the obvious, but this would be my first guess for the cause of your problem.

As a matter of practice, however, I'd recommend adding error handling for the procedure as a whole so that any unhandled errors go to the error handler where things are cleaned up.

Anyway, after successful execution of this procedure, do you have an instance of Excel open every time? I ask that question with the assumption that you have verified that there was no instance of Excel open prior to running the procedure.

Actually, I used to ignore this problem before but as a need to execute consecutive excel applications arises I have no alternative except to fix the problem.

My code, for some reasons, stops at the middle of the program if the previous excel application is still running. It closes all the visible excel files as it should, that's why I know that it is reaching the end of the code, however, everytime the execution end an excel file is still running in the background.

Regards,

Have you debugged it line by line watching the task manager to see what's creating the apparently second instance of Excel?

It does not create a second instance of Excel, it just does not close the image name. Under Windows Task Manager/Applications you will not see any Excel running but under Windows Task Manager/Processes you will see "EXCEL.EXE" running. And when I end this file, my code can ran again.

Are you saying that when you reach ex2.quit it doesn't disappear from the task manager?

Quote:

---

Originally Posted by xCav8r

Would you edit that and enclose the code in vba tags?

---

I have taken the liberty to do so. :)

[uvba]here[/uvba]

Hi,

What You need to do is propably the following:

[vba]
Option Explicit
Sub Check_If_Running_Instance()
Dim ex2 As Excel.Application

On Error Resume Next
Set ex2 = GetObject(, "Excel.Application")
If Err.Number = 429 Then
Set ex2 = CreateObject("Excel.Application")
Err.Clear
End If
On Error GoTo 0

'If You want to show the instance to the users then add the following:
With ex2
.UserControl = True
.Visible = True
End With

ex2.Quit
Set ex2 = Nothing

End Sub
[/vba]

Check out the helpfile by selecting the object in the VB-editor and hit the F1-button.

Kind regards,
Dennis

Dennis, that's very helpful code in general, but he still needs to find where in his code a second instance of Excel is being created before he can implement your suggestion, assuming that he's creating a new instance of the object (which would appear to be the case).

Hi,

THe OP's code does not create a second instance of Excel but as You can see the following snippet code does not close the created instance of Excel:

[vba]
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
Exit Sub
End If
[/vba]

So the OP needs a better basic error handling which I thought You should had been able to track down ;)

[vba]
Option Explicit
Sub Check_If_Running_Instance()
Dim ex2 As Excel.Application
Dim wb2 As Excel.Workbook

On Error Resume Next
Set ex2 = GetObject(, "Excel.Application")
If Err.Number = 429 Then
Set ex2 = CreateObject("Excel.Application")
Err.Clear
End If
Set wb2 = ex2.Workbooks.Open(strInputFileName, False)
If Err.Number = "'The received error for not being able to open the file" Then
MsgBox "Message..."
Exit_Handling
End If
On Error GoTo 0

'If You want to show the instance to the users then add the following:
With ex2
.UserControl = True
.Visible = True
End With

Exit_Handling:
ex2.Quit
Set wb2 = Nothing
Set ex2 = Nothing

End Sub
[/vba]

If the above basic error handling have not yet been documented in a KB-article then I strongly suggest that someone create an entry for it.

Kind regards,
Dennis

Thanks a lot for all your help guys. I was able to run my code by explicitly specifying the object where the execution usually stops, e.g. ex2.cells or ex2.columns.

I will try to change my error handling code based on the last suggestion and I will let you know if that resolved my problem.

Thx Again

Quote:

---

Originally Posted by XL-Dennis

THe OP's code does not create a second instance of Excel but as You can see the following snippet code does not close the created instance of Excel:

---

I wasn?t so willing to make that assumption after in post #4 he said that he didn?t paste all of the code. In post #10 he wrote ?that's why I know that it is reaching the end of the code?, which is why I asked for him to explicitly say that it was reaching the line with ex2.quit, because I had already expressed the need for different error handling and already said that an error occurring before ex2.quit would cause it not to quit.

Quote:

---

Originally Posted by XL-Dennis

So the OP needs a better basic error handling which I thought You should had been able to track down ;)

---

From my very first response I?ve been advocating exactly that. In post #2 I wrote: ?Perhaps you had created them in your code but got an error that prevented .quit from executing? On good rule of thumb is when automating other apps in office, always use an error handler that cleans up by either making them visible or quiting things that are open.?

In post #9 I wrote: ?If the code it stopped for any reason before it has a chance to make it to the end of the procedure, then Excel won't quit.?

and also...

?As a matter of practice, however, I'd recommend adding error handling for the procedure as a whole so that any unhandled errors go to the error handler where things are cleaned up.? :dunno

The modified error handling procedure closed "EXCEL.EXE". However, at the end of my code, where I got all I need to have, an error displayed, "Error number 0". It seems that Exit_Handling is being executed at the end even when no routine is calling it. Why?

Exit_Handling is just a label. It's not a separate procedure. You can use lables with goto statements, but otherwise, they're ignored when a procedure is executed.

Thanks a lot.

You must have an Exit Sub just before the handler label

That would work. Thanks.

Mr. Roarke, I think that depends on how you choose to handle things, but generally, most people follow this basic model:

[VBA] Function ProcedureName(ArgumentList) As DataType
' Procedure comments.
' Declare local variables and constants.

On Error GoTo ProcedureName_Err
' Procedure code.
.
.
.
ProcedureName = True (or some other return value)
ProcedureName_End:
' Cleanup code that closes open files and sets object variables = Nothing.
Exit Function
ProcedureName_Err:
ProcedureName = False
Select Case Err.Number
Case AnticipatedError#1
' Handle error #1.
Case AnticipatedError#2
' Handle error #2.
Case UnAnticipatedErrors
' Handle unanticipated error.
Case Else
' Handle unforseen circumstances.
End Select
Resume ProcedureName_End
End Function [/VBA]

For anyone interested, see http://msdn.microsoft.com/library/de...lingerrors.asp for more details.

Quote:

---

Originally Posted by JuanDelaCruz

That would work. Thanks.

---

Don't put exit sub before ex2.quit though. :whip

Aye, xCav8r, I have seen and used that form as well. I suggested the simplest approach, based on the original code samples submitted. Thanks for the clarification, though.

Juan, if your problem is solved, can you use the link at the top of this thread (called Threat Tools) to mark this as solved?