Excel COM对象 - 处理工作簿关闭事件 [英] Excel COM object - handling workbook close event

问题描述

我使用 QAxObject 自动化来自Qt应用程序的Excel窗口。我需要在用户关闭Excel窗口时收到通知。

Excel工作簿COM对象有一个事件 BeforeClose（）但是处理它是不够的，因为它在用户被提示保存更改之前被触发，因此用户将在保存确认对话框中取消关闭操作。因此，即使 BeforeClose（）被触发，Excel窗口也不能关闭。

还有另一个事件 Deactivate（）当工作簿被停用时触发。当窗口实际关闭时以及当用户切换到不同的工作簿时，这被触发。因此单独处理它也是不够的。

我目前使用的解决方案是设置一个bool变量关闭 true当 BeforeClose（）被触发。当 Deactivate（）事件处理程序随后被触发时，我认为只有当关闭设置为true时，Excel才会关闭。 / p>

这需要一件事，如果用户在保存确认对话框中取消关闭操作，则将关闭设置为false。我使用的方法是在 BeforeClose（）中启动重复计时器并检查Excel COM对象的 Visible 属性定时器。当打开保存确认对话框等模态对话框时， Visible 属性将被评估为false。因此，一旦可见，我就可以将关闭设置为 false 属性在计时器中被评估为 true

code

有人知道更好的方式在Excel工作簿实际关闭时获得通知

解决方案

解决方法是绕过Excel的提示，如下（vb代码）：

  Private Sub Handler_WorkbookBeforeClose（wb As Workbook，ByRef cancel As Boolean）
 
如果wb.Saved = False则
 
 Dim answer As MsgBoxResult = MsgBox（你想保存对+ wb.Name +？，MsgBoxStyle.YesNoCancel）的更改
 
选择案例答案
案例MsgBoxResult。是
 wb.Save（）
案例MsgBoxResult.No 
 wb.Saved = True 
案例MsgBoxResult.Cancel 
 cancel = True 
退出Sub 
结束选择
 
结束如果
 
'在这里放置您的代码在工作簿关闭时执行。 
 
 End sub 
  

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?

解决方案

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

这篇关于Excel COM对象 - 处理工作簿关闭事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！