如何关闭空白excel工作簿与宏工作簿?

I have 2 Excel workbooks one contains Macros and the other workbook calls Macro workbook.

我有两个Excel工作簿，一个包含宏，另一个工作簿调用宏工作簿。

In main workbook open event Macro workbook will be opened in the same application instance and when the workbook closes I am closing Macro workbook and after that I have written Appication.Quit, but here the problem is after closing Macro workbook one blank excel remians open.

在主工作簿中，打开事件宏工作簿将在相同的应用程序实例中打开，当工作簿关闭时，我将关闭宏工作簿，然后编写Appication。退出，但是这里的问题是在关闭宏工作簿后打开一个空白excel remians。

How to close the blank workbook through VBA?

如何通过VBA关闭空白工作簿?

By the way I am facing this issue in Office 2007 and 2010 only in 2013 there is no blank excel.

顺便说一下，我在Office 2007和2010都遇到过这个问题，只是在2013年才没有blank excel。

Below is the code in Macro.xlsm - Module1

下面是宏中的代码。xlsm——Module1

Public Sub Test()
MsgBox "Test"
End Sub

Public Sub Auto_Close()
ThisWorkbook.Saved = True
End Sub

Below is the code in Test.xlsm - Thisworkbook

下面是测试中的代码。xlsm——Thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Workbooks("Macro.xlsm").Close
Module1.CodeFileClose
End Sub

Private Sub Workbook_Open()
Module1.CodeFileOpen
End Sub

Below is the code in Test.xlsm - Module1

下面是测试中的代码。xlsm——Module1

Public Sub CodeFileOpen()
Application.Workbooks.Open ("C:\Macro.xlsm")
Application.Run "Macro.xlsm!Test"
End Sub

Public Sub CodeFileClose()
MsgBox "Before Close"
Application.Quit
End Sub

3 个解决方案

#1

Application.Quit

, or

,或

thisworkbook.close

will trigger again your workbook_beforeclose event !!!!

将再次触发您的workbook_beforeclose事件!!!

so you will be looping it

所以你会循环使用它

in test.xlsm , thisworkbook section :

在测试。xlsm，本册:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'if you get in here, workbook is already going to close

on error resume next 'if macro.xlsm not opened it would cause an error

thisworkbook.saved= true ' (=no save) or false(=save) or delete line if you want to be asked

with Application
    .displayalerts=false 'set to true if want to be asked before saving
    .enableevents=false 'block events from both workbooks
    .Workbooks("Macro.xlsm").Close
    .enableevents=true 'enable events from both workbooks
end with

'Module1.CodeFileClose 'will cause looping (except if you add application.enableevents=false, but then it will be the case for all excell and you wont be able to triger events, or reset it to true by code)

End Sub

#2

From my understanding, you want to close your macro Workbook along with your Workbook that initially opened the Workbook.

根据我的理解，您希望关闭宏工作簿和最初打开工作簿的工作簿。

If that is right, why don't you try in your CodeFileClose() Sub the following code:

如果这是对的，为什么不试试你的CodeFileClose()子代码:

Public Sub CodeFileClose()
    MsgBox "Before Close"
    Workbooks("Macro.xlsm").Close
End Sub

If I'm wrong please give a few more details on your problem.

如果我说错了，请就你的问题再多说几句。

#3

Here is how I did it. The trick is to close the parent of the application with Application.Parent.Quit:

我是这样做的。诀窍是用application . parent(父母)关闭应用程序的父类。

Sub Close_the_whole_excel_window()

Dim New_session_Excel as New Excel.Application

New_session_Excel.Workbooks.Open Filename:=(Path&FileName), ReadOnly:=True

New_session_Excel.Quit ' This will close the workbook and leave an extra Excel window that stays behind

New_session_Excel.Parent.Quit ' This will close the extra Excel window that stays behind

End Sub

#1

Application.Quit