Excel COM object - handling workbook close event

Question

I am automating an Excel window from a Qt application using QAxObject. I need to get notified when the Excel window is closed by the user.

Excel workbook COM object has an event BeforeClose() but handling it is not sufficient because it is fired before user is prompted to save changes, so user would cancel the close operation in save confirmation dialog. So, even BeforeClose() is fired, Excel window may not be closed.

There is another event Deactivate() which is fired when a workbook is deactivated. This is fired when a window is actually closed as well as when user switches to a different workbook. So handling it alone is also not sufficient.

The solution I am using currently is setting a bool variable closing to true when BeforeClose() is fired. When Deactivate() event handler is subsequently fired, I consider that Excel is closed only if closing is set true.

This needs one more thing, setting closing to false if user cancels close operation in save confirmation dialog. The method I used for that is starting a repeating timer in BeforeClose() and check Visible property of Excel COM object in the timer. Visible property is evaluated to false when a modal dialog such as save confirmation dialog is open. So I can set closing to false as soon as Visible property evaluated to true in timer.

Does anybody know a better way to get notified when Excel workbook is actually closed?

Answer 1

A workaround is to bypass Excel's prompt like this (vb code):

Private Sub Handler_WorkbookBeforeClose(wb As Workbook, ByRef cancel As Boolean)

    If wb.Saved = False Then

        Dim answer As MsgBoxResult = MsgBox("Do you want to save the changes you made to " + wb.Name + "?", MsgBoxStyle.YesNoCancel)

        Select Case answer
            Case MsgBoxResult.Yes
                wb.Save()
            Case MsgBoxResult.No
                wb.Saved = True
            Case MsgBoxResult.Cancel
                cancel = True
                Exit Sub
        End Select

    End If

'Put here your code to be executed when workbook has been closed.

End sub