Closing a Workbook Without a Prompt

The Close method is used to close a workbook

ThisWorkbook.Close

If the workbook is “dirty” (needs to be saved), Excel will display its standard prompt

SaveMsg.gif

To avoid seeing this message, you can 1) Save the file first, 2) Change the DisplayAlerts property, 3) Use the SaveChanges argument of the Close method, or 4) set the Saved property to True.

Save the File First

ThisWorkbook.Save
ThisWorkbook.Close

Change DisplayAlerts

Application.DisplayAlerts = False
ThisWorkbook.Close

Note that this won’t save the changes, it will close the workbook without saving. Also note that since I’m closing ThisWorkbook, I don’t set DisplayAlerts back to True. The workbook with the code will be closed and that line would never execute. If you’re closing a different workbook, it’s a good idea to set DisplayAlerts back to True. That said, this is the worst option of the three and I never use it.

Using The Argument

ThisWorkbook.Close SaveChanges:=True

You can set SaveChanges to either True of False depending on what you want to do.

Saved Property

ThisWorkbook.Saved = True
ThisWorkbook.Close

Setting Saved to True tells Excel that the workbook does not need to be saved, even if it does. Needless to say, the workbook will be closed with changes since the last save lost with this method.

Personally, I use method 3 almost exclusively.

11 thoughts on “Closing a Workbook Without a Prompt

  1. The third method is good, but if you want to close the workbook and make sure it doesnt save, the fourth method looks great.

  2. Mark, you can use method 3 for that too, just read Dick’s comments again… just change the SaveChanges parameter to False, like this:

    ThisWorkbook.Close SaveChanges:=False

  3. Joseph: If the workbook is unsaved, you need to use the SaveAs method

    ThisWorkbook.SaveAs “C:Mybook.xls”

    to save it before you Close.

  4. In Excel 2000 SP3 we have run accross a situation where we cannot set the Saved property to True. We have a COM add-in and after stepping through the code we discovered that the Saved property is still False right after the line the sets it to True is exectued. This is occuring in the OnBeforeClose event handler. Has anyone experienced this or have any recommendations on how to handle this?

  5. What if I want to close a workbook that I’m not working in? For example, I’m opening a workbook, copying some values, and then I want it to close without doing anything but running a macro. I tried a “workbook.close (sPath & workbook.xls)” but no such luck…. any ideas?!?

  6. Here’s the syntax you need:

    Workbooks(“bookname.xls”).Close

    You don’t need the path. If you have set a variable to the workbook, you can use the variable:

    wbWorkbook.Close

    – Jon

  7. Dear Sir,
    I am Using Excel File in VB.
    I closed the Excel File before that i Saved the Excel.
    I Could not open the Excel file Until i come out from the VB Application.

    xlWorkbook.saveas (Path)
    xlWorkbook.close
    set xlWorkbook=nothing
    set xlApplication=Nothing

    Regards,
    Varatharajan.R

  8. Is there a way to stop the prompt from appearing when the spreadsheet is opened in Internet Explorer?

    The examples above only seem to work when the spreadsheet is opened in Excel.

  9. I have jus started learning vba programming, and I had been trying to do this:
    I want to protect all the sheets in a workbook when I close the workbook. How can I do that?

  10. Try something like this:

    Sub Protect_close_spreadsheets()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    ws.Protect Password:=”pswd”, DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next

    ThisWorkbook.Close SaveChanges:=True

    End Sub


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.