Excel VBA: Close Workbook Without Saving

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can use Excel VBA to close a workbook without saving.


Excel VBA: Close Workbook Without Saving (Quick View)

Sub Close_Workbook_Without_Saving()

Book_Name = "Close Workbook Without Saving.xlsm"

On Error GoTo Message

Workbooks(Book_Name).Close SaveChanges:=False

Message:
    MsgBox "The Workbook is Already Closed", vbExclamation

End Sub

VBA Code to Close Workbook Without Saving in Excel VBA


An Overview to Close Workbook Without Saving Using Excel VBA (Step-by-Step Analysis)

Here, I’ve got a workbook called “Close Workbook Without Saving.xlsm”.

Workbook to Close Workbook Without Saving Using Excel VBA

Our objective is to develop a VBA code to close the workbook without saving. I am showing you the step-by-step analysis of the code for your better understanding.

⧪ Step 1: Taking Necessary Input

First, we must insert the necessary input into the code. There is only one input required in this code. That is the name of the workbook that we’ll close.

Here it’s “Close Workbook Without Saving.xlsm”.

Book_Name = "Close Workbook Without Saving.xlsm"

Taking Input to Close Workbook Without Saving Using Excel VBA


⧪ Step 2: Closing the Workbook

This is the most important step. We’ll close the workbook using the Close method of VBA. To close it without saving, we’ll set the SaveChanges parameter of the Close method to False.

But there may be cases while you are trying to close the workbook in VBA, but the workbook is already closed. In those cases, VBA will raise an error.

Therefore, to protect the code from any possible error, we’ll wrap it with an alternative solution and point it to move to that solution if necessary.

On Error GoTo Message

Workbooks(Book_Name).Close SaveChanges:=False

Read More: Excel VBA: Save and Close Workbook


⧪ Step 3: Alternative Solution in Case of Error

Finally, we’ll fix the alternative solution in case any error actually raises. The solution is nothing but a simple message stating that the workbook is already closed.

Message:
    MsgBox "The Workbook is Already Closed", vbExclamation

Handling Error to Close Workbook Without Saving Using Excel VBA

Therefore, the complete VBA code will be:

VBA Code:

Sub Close_Workbook_Without_Saving()

Book_Name = "Close Workbook Without Saving.xlsm"

On Error GoTo Message

Workbooks(Book_Name).Close SaveChanges:=False

Message:
    MsgBox "The Workbook is Already Closed", vbExclamation

End Sub

VBA Code to Close Workbook Without Saving in Excel VBA


Developing the Macro to Close a Workbook Without Saving Using Excel VBA

We’ve seen the step-by-step analysis of the code to close a workbook without saving using VBA.

Now let’s see how to develop a Macro to run the code.

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

Inserting Module to Close Workbook Without Saving Using Excel VBA

⧪ Step 3: Putting VBA Code

This is the most important step. Insert the given VBA code in the module. Change the name of the workbook to the one you require.

⧪ Step 4: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

The workbook will close without saving the last changes.

Read More: How to Close Workbook at Specific Time Using Excel VBA


Things to Remember

  • The Workbook.Close method of VBA uses a total of 3 parameters. We’ve used only the first one, the SaveChenges parameter. Along with it, it used 2 more parameters called FileName and RouteWorkbook.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Therefore, this is the process of closing a workbook without saving using Excel VBA. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo