Andy laid this down on his screen :
I ended up using
ActiveWorkbook.Close Savechanges:=False
to close the offending workbook and this allowed the remaining
workbooks to resume and shutdown gracefully.
My take at this point is that Application.Quit works fine in a single
workbook and if multiple workbooks are present then something like
ActiveWorkbook.Close Savechanges:=False
Workbooks("Name_01.xls").Close Savechanges:=False
is the way to go.
Thank you all for the comments.
Andy
Actually, I iterate the workbooks collection to close all open
workbooks (allowing option to save) before executing the
"Application.Quit" statement. Since my apps usually run in their own
automated instance of Excel, all shutdown cleanup mimics what Excel
would normally do if you shut it down with multiple workbooks open.
Here's an example:
Sub ShutdownApp()
On Error Resume Next
If lCountVisibleWorkbooks() > 0 Then
Dim Wkb As Workbook, vAns As Variant
Dim bUserCancel As Boolean, bSave As Boolean
With Application
.EnableEvents = False: .DisplayAlerts = False
End With
For Each Wkb In Application.Workbooks
If Wkb.Windows(1).Visible Then
If Not Wkb.Saved Then
vAns = MsgBox("Do you want to save changes to " _
& Wkb.Name & "?", _
vbYesNoCancel + vbExclamation, gsAPP_NAME)
Select Case vAns
Case Is = vbNo: Wkb.Close False
Case Is = vbYes: Wkb.Close True
Case Else: bUserCancel = True: GoTo CancelExit
End Select
Else
Wkb.Close False
End If
End If
Next
End If
CancelExit:
With Application
.EnableEvents = True: .DisplayAlerts = True
End With
If bUserCancel Then Exit Sub
'If we got here then we're ready to quit
gbShutdownInProgress = True
Application.Visible = False '//hide any cleanup/restore activity
If Application.Version >= 12 Then _
Workbooks("ui12su.xlam").Close Else ResetExcelUI
HandleEvents("Stop")
Call ResetCommandBars: ResetBuiltInBars: RestoreExcelSettings
Application.Quit
End Sub
