VB.NET 关闭 Excel

VB.NET Closing Excel

提问by TypeM1smatch

I'm extremely new to Visual Basic (and to this site) and need a little advice on how to properly exit Excel when using automation. A little background on my issue: we have a large Excel workbook that my boss has created to do hundreds of relatively complex calculations. Currently users are just using the spreadsheet to input their data, but right now I'm working on a vb.net user interface to allow them to enter their data, and to also provide us with more flexibility and features to make their lives easier and get them out of the horror that is Microsoft Excel.

我对 Visual Basic（和本网站）非常陌生，需要一些关于在使用自动化时如何正确退出 Excel 的建议。关于我的问题的一点背景：我们有一个很大的 Excel 工作簿，我的老板创建了它来进行数百个相对复杂的计算。目前用户只是使用电子表格来输入他们的数据，但现在我正在开发一个 vb.net 用户界面，以允许他们输入他们的数据，并为我们提供更多的灵活性和功能，使他们的生活更轻松和让他们摆脱 Microsoft Excel 的恐惧。

Upon opening my .net app, I'm running a command to open the workbook in the background (the visible property of the Excel object is set to false) so we can use it. At some point we will migrate these calculations into the stand alone app, but for now that is not a cost effective solution so we're going to use the spreadsheet that's already developed. I've run into a little issue upon closing Excel. I'm able to close Excel using the FormClosing event without a hitch. My problem is this: if I have my application running (which means my desired workbook is open in the background) and I open up another workbook via a shortcut on my desktop, when I go to close my application while that other workbook is open, it tries to close that 2nd workbook and the user is prompted with Excel's save changes dialog box. How do i get around this? Am I going to need to locate the process that is specific to my calculation workbook and close that rather than closing the Excel object I created?

打开我的 .net 应用程序后，我正在运行一个命令在后台打开工作簿（Excel 对象的可见属性设置为 false），以便我们可以使用它。在某个时候，我们会将这些计算迁移到独立的应用程序中，但目前这不是一个具有成本效益的解决方案，因此我们将使用已经开发的电子表格。我在关闭 Excel 时遇到了一个小问题。我可以使用 FormClosing 事件顺利关闭 Excel。我的问题是：如果我的应用程序正在运行（这意味着我想要的工作簿在后台打开）并且我通过桌面上的快捷方式打开另一个工作簿，当我在另一个工作簿打开时关闭我的应用程序，它尝试关闭第二个工作簿，并提示用户使用 Excel 的保存更改对话框。我如何解决这个问题？我是否需要找到特定于我的计算工作簿的进程并关闭它而不是关闭我创建的 Excel 对象？

Here's my code for my FormClosing event:

这是我的 FormClosing 事件的代码：

Private Sub Form8_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

 'close the workbook

xlWorkbook.Close(SaveChanges:=False)

'clean up

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook)

xlWorkbook = Nothing

'close the excel application

xlApp.Quit()

'clean up

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

xlApp = Nothing

End Sub

EDIT: I'm aware this issue is happening because of the way Excel opens it's files. Unless you go to the start menu and open a new instance of Excel, Excel will open your file in the application object that is already running. This leads me to believe this issue may be unavoidable, but would love some feedback even if you're just brainstorming

编辑：我知道这个问题的发生是因为 Excel 打开它的文件的方式。除非您转到开始菜单并打开 Excel 的新实例，否则 Excel 将在已运行的应用程序对象中打开您的文件。这让我相信这个问题可能是不可避免的，但即使你只是头脑风暴，也会喜欢一些反馈

Any advice is greatly appreciated. Thanks, Eric

任何意见是极大的赞赏。谢谢，埃里克

PS, thanks to whomever put my code in code tags!! I promise I will figure out how to do that myself ;)

PS，感谢谁把我的代码放在代码标签中！！我保证我会自己弄清楚如何做到这一点；)

采纳答案by NickSlash

You could add some code to the Workbook_BeforeCloseto prevent the book being closed when its needed.

您可以向 中添加一些代码以Workbook_BeforeClose防止书在需要时被关闭。

You can do this a number of ways, the simplest would be to add it directly into your worksheet, you might also be able to hook the event in vb.net (public withevents type thing, ive only done it in VBA so couldnt help with the .net variation)

您可以通过多种方式执行此操作，最简单的方法是将其直接添加到您的工作表中，您也可以在 vb.net 中挂钩该事件（公共事件类型的事情，我只在 VBA 中完成，所以无法帮助.net 变体）

Here's a simple VBA solution that might work.

这是一个可能有效的简单 VBA 解决方案。

Module - Give it a name eg: Main

模块 - 给它一个名字，例如：Main

Public Sub UnlockWorkbook()
Dim Window As Window

For Each Window In ThisWorkbook.Windows
    Window.Visible = True
Next Window

End Sub

Public Sub LockWorkbook()
Dim Window As Window

For Each Window In ThisWorkbook.Windows
    Window.Visible = False
Next Window

End Sub

ThisWorkbook Module

本工作簿模块

Private Sub Workbook_BeforeClose(Cancel As Boolean)

For Each Window In ThisWorkbook.Windows
    If Window.Visible = False Then
        Cancel = True
        If Application.Workbooks.Count = 1 Then
            Application.Visible = False
        End If
        Exit For
    End If
Next Window

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SaveAsUI = False
End Sub

When you load the workbook, using your application, call the "LockWorkbook" macro which will hide the workbook, and you can also hide the application too.

当您使用您的应用程序加载工作簿时，调用“LockWorkbook”宏将隐藏工作簿，您也可以隐藏应用程序。

Now if a user opens another workbook, unhiding the application, when they close their workbook and try to close the application it should stop that action, and re-hide the application for you.

现在，如果用户打开另一个工作簿，取消隐藏该应用程序，当他们关闭他们的工作簿并尝试关闭该应用程序时，它应该停止该操作，并为您重新隐藏该应用程序。

When you do want to close the workbook, you need to call the "UnlockWorkbook" macro which will allow the "Worksheet_BeforeClose" to complete without setting Cancel to true.

当您确实想要关闭工作簿时，您需要调用“UnlockWorkbook”宏，这将允许“Worksheet_BeforeClose”完成而不将取消设置为 true。