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 Comments

  1. Mark says:

    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. Juan Pablo says:

    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 says:

    if i’m using method 3, how do i set the workbook name before i closed it.

  4. Dick says:

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

    ThisWorkbook.SaveAs “C:Mybook.xls”

    to save it before you Close.

  5. Shane says:

    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?

  6. JD says:

    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?!?

  7. Jon Peltier says:

    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

  8. Varatharajan says:

    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

  9. Graham says:

    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.

  10. candice says:

    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?

  11. JtL says:

    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 or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: