Closing an Excel Automation Object
After the user clicks the Close Excel command button, the CloseExcel subroutine is called, as shown in Listing 22.5. The subroutine first checks to see whether the gobjExcel object variable is still set. If it is, Excel is still running. The DisplayAlerts property of the Excel application object is set to False. This ensures that, when the Quit method is executed, Excel will not warn about any unsaved worksheets. This methodology is acceptable because all work was accomplished using a new instance of the Excel application object. If you want to save your work, you should execute the required code before the Quit method is executed.
Listing 22.5 The CloseExcel Subroutine
Sub CloseExcel()
'Invoke error handling
On Error GoTo CloseExcel_Err
'If the Excel object variable is still set,
'turn of alerts and quit Excel
If Not gobjExcel Is Nothing Then
gobjExcel.DisplayAlerts = False
gobjExcel.Quit
End If
CloseExcel_Exit:
'Destroy the Excel object variable
Set gobjExcel = Nothing
Exit Sub
CloseExcel_Err:
'Display error message and resume at Exit routine
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume CloseExcel_Exit
End Sub