Closing Excel Application using VBA

41
votes

I have used the following without success. The active workbook closes, indeed, but the excel window remains open.

Application.ActiveWindow.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False

Which is the command that terminates the application?

EDIT

To say a little more: In the workbook Open event I run a macro. I want to terminate the application when that macro finishes. I also tried this without success.

Private Sub Workbook_Open()
   Macro_MyJob
   Application.Quit
End Sub

Where should I put this Application.Quit command?

8
excelvbaexcel-2007

8 Answers

62
votes

I think your problem is that it's closing the document that calls the macro before sending the command to quit the application.

Your solution in that case is to not send a command to close the workbook. Instead, you could set the "Saved" state of the workbook to true, which would circumvent any messages about closing an unsaved book. Note: this does not save the workbook; it just makes it look like it's saved.

ThisWorkbook.Saved = True

and then, right after

Application.Quit
13
votes

To avoid the Save prompt message, you have to insert those lines

Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

After saving your work, you need to use this line to quit the Excel application

Application.Quit

Don't just simply put those line in Private Sub Workbook_Open() unless you got do a correct condition checking, else you may spoil your excel file.

For safety purpose, please create a module to run it. The following are the codes that i put:

Sub testSave()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit
End Sub

Hope it help you solve the problem.

6
votes
Application.Quit

Should do the trick.

6
votes
Sub TestSave()
Application.Quit
ThisWorkBook.Close SaveChanges = False
End Sub

This seems to work for me, Even though looks like am quitting app before saving, but it saves...

2
votes

I tried a certain sequence that seems to work as you can see below:

ThisWorkbook.Saved = True
Application.Quit
Application.ActiveWindow.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
1
votes

You can try out 

ThisWorkbook.Save
ThisWorkbook.Saved = True
Application.Quit
1
votes

In my case, I needed to close just one excel window and not the entire application, so, I needed to tell which exact window to close, without saving it.

The following lines work just fine:

Sub test_t()
  Windows("yourfilename.xlsx").Activate
  ActiveWorkbook.Close SaveChanges:=False
End Sub
-3
votes
Sub button2_click()
'
' Button2_Click Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    ActiveSheet.Shapes("Button 2").Select
    Selection.Characters.Text = "Logout"
    ActiveSheet.Shapes("Button 2").Select
    Selection.OnAction = "Button2_Click"
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Save
    Application.Quit
End Sub