Excel does not completely close when using Workbook.Close

Question

In the macro I'm running, I end with closing the last Excel Workbook that's open. When I run it everything closes except one window which doesn't have a spread sheet, just a light-blue backdrop. Am I using a wrong command or is there just no way to close all open windows?

Here is a post asking my exact question, but the solution isn't working for me.

Here are the last two commands that I'm using:

'
' Closes Populated JD Form
'
    Workbooks(name).Close SaveChanges:=False

'
' Closes Transfer Template
'
    Workbooks("Transfer Template.xlsm").Close SaveChanges:=False

Thanks in advance!!!

@DeanOC Under the last line, correct? If so, it didn't work. — bdkong, Jul 15 '15 at 20:40
It worked for me @bdkong - running `Application.Quit` gave me a popup "Do you want to save?". This can be disabled by adding `Application.DisplayAlerts = False`, but i don't think you will need it in your case. — user1274820, Jul 15 '15 at 21:13
Hmm, what do you mean `Application.quit` doesn't work? Does it give an error? Does it close the workbook, but not the application? What happens if you run this command in the Immediate window? — vacip, Jul 17 '15 at 21:19

Dan Donoghue · Accepted Answer · 2015-07-16T00:36:30.770

7

Don't turn the display alerts off as it will suppress any genuine questions.

Use this instead

ActiveWorkbook.Saved = True
Application.Quit

This will stop Excel for asking about saving changes as it now thinks that it has been saved already. A good example of this is your personal macro workbook, I use mine a LOT and am always creating or modifying code in there. If I turn the alert off and there are changes in my personal workbook, I lose them all.

Try not to bulk fix possible scenarios where possible. Another example is errors, have an error trapping routine and deal with it as needed as opposed to a blanket statement of on error resume next.

edited Jul 16 '15 at 00:36

answered Jul 16 '15 at 00:23

Dan Donoghue

6,056
2
18
36

I don't understand how this is supposed to fix my trouble with @DeanOC 's answer. First of all I don't want to save changes. Secondly, it doesn't give me the option to save because I've already denoted that I don't want to save it. – bdkong Jul 16 '15 at 12:21
It doesn't save it it just fools excel into thinking it has been saved so it closes without prompting you – Dan Donoghue Jul 16 '15 at 12:48
Ahh, I see what you're saying. It's still not working though :/ – bdkong Jul 16 '15 at 13:44
Found my problem. I had a bit of code that was slightly above the app quit code that was closing the workbook. Deleted that and it worked fine. – bdkong Jul 17 '15 at 21:20

score 1 · Answer 2 · answered Jul 15 '15 at 22:16

To make sure the question doesn't remain unanswered, using both suggestions

(credit to @DeanOC and @user1274820)

'Closes Populated JD Form
Workbooks(Name).Close SaveChanges:=False

'Closes Transfer Template
Workbooks("Transfer Template.xlsm").Close SaveChanges:=False

With Application
    .DisplayAlerts = False
    .Quit
End With

score 0 · Answer 3 · edited Mar 31 '19 at 21:02

Once I removed atpvbaen.xls from my references, application.quit worked as advertised.

I was tearing my hair out - until I realized I had atpvbaen.xls set as a reference - which was ANOTHER excel thread left alive. I didn't need it for this application, so I disabled it. In the VB Editor, Tools => References un-check atpvbaen.xls. If I had needed it, maybe shutting that one down deliberately would have worked.

Excel does not completely close when using Workbook.Close

3 Answers3