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
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.
The third method is good, but if you want to close the workbook and make sure it doesnt save, the fourth method looks great.
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
if i’m using method 3, how do i set the workbook name before i closed it.
Joseph: If the workbook is unsaved, you need to use the SaveAs method
ThisWorkbook.SaveAs “C:Mybook.xls”
to save it before you Close.
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?
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?!?
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
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
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.
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?
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