How to close blank excel workbook along with Macro workbook?

1
votes

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

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.

How to close the blank workbook through VBA?

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

Below is the code in Macro.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

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

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
4
vbaexcel
Testing this. Is your intention to close the Macro book manually or should the Macro Test routine close it? - Jimmy Smith
I wil close only Test.xlsm and the Macro workbook will be closed throgh Sub - superuser

4 Answers

1
votes
Application.Quit

, or 

thisworkbook.close

will trigger again your workbook_beforeclose event !!!!

so you will be looping it

in test.xlsm , thisworkbook section :

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
0
votes

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:

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.

0
votes

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

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
0
votes

I got it to work by closing the parent first and then the application:

ActiveWorkbook.Save
Application.DisplayAlerts = False

Excel.Parent.Quit
Application.Quit