Close a workbook using VBA in Microsoft Excel

 

In this article we will learn how to excel close workbook using VBA in Microsoft Excel 2010.

After your VBA macro / code has performed all the required actions, you would want the macro to either save the file or skip saving it.  So here are the options you can set in the code while asking it to close the file. The Close and Open method in VBA can be used to close and open workbooks.

If you want the code to just close the file without saving the changes – then use this line at the end of your code or wherever needed -

ActiveWorkbook.Close (False) or ActiveWorkbook.Close False or ActiveWorkbook.CloseSaveChanges:=FALSE

See the code in this picture below.  To copy the code, press Alt + F11 on the keyboard while ensuring that the excel sheet is open.  Then right click on Microsoft Excel Objects and select Insert.  Then click on Module.  Copy over the code given below the picture -

 

img1

Option Explicit

Sub macro1()

ActiveWorkbook.Close False

End Sub

 

Note: You can change the text ActiveWorkbook to refer to the name of the workbook or the variable you have used to identify the workbook as per your code.

If you want the code to save the changes while closing the file, then use

ActiveWorkbook.Close (True) or ActiveWorkbook.Close True or ActiveWorkbook.Close SaveChanges:=TRUE

 

img2

Here is the code –

 

Option Explicit

Sub macro1()

ActiveWorkbook.Close True

End Sub

 

However, if you want the user to decide, then you just need to enter

ActiveWorkbook.Close

 

img3

Here is the code –

 

Option Explicit

Sub macro1()

ActiveWorkbook.Close

End Sub

 

The user will get a popup asking if they want to save the workbook or not.

 

img4

 

 

 

 

Workbooks(“File1.XLS”).Close SaveChanges:=False

This example will close File1.xls and discards any changes that have been made to it.

 

Conclusion:

If “Application.DisplayAlerts” has been set to False, you will not get any popup and the workbook will directly close without prompting to save.

This popup will appear if “Application.DisplayAlerts” is not set to False anywhere before the excel vba Activeworkbook.Close line of code.

So even if the code requires that you switch off the alerts, ensure that “Application.DisplayAlerts = True” is present before the Activeworkbook.Close line.

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com



15 thoughts on “Close a workbook using VBA in Microsoft Excel

  1. The “active” workbook is calling the command for another workbook to close, which it does, I have looked with task manager. But it does not save changes as I am expecting it to do. The time never increments.

  2. Try this:

    Sub Auto_close()
    Application.ScreenUpdating = False
    Application.Quit
    ThisWorkbook.Close SaveChanges:=True
    Application.ScreenUpdating = True
    End Sub

    • Or:

      Sub Auto_close()
      Application.ScreenUpdating = False
      ThisWorkbook.Close SaveChanges:=True
      Application.ScreenUpdating = True
      End Sub

  3. Close workbook and Excell if only one workbook is open:

    ————————————————————–
    Dim mCount As Integer
    Tel = 0
    For Each c In Application.Workbooks()
    With c
    mCount = mCount + 1
    End With
    Next c
    If mCount = 1 Then
    Application.Quit
    ActiveWorkbook.Close False
    Else
    ActiveWorkbook.Close False
    End If
    End Sub
    ————————————————————–

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>