You may be familiar with VBA’s Workbook.Saved property. It’s read/write and lets you determine, or set, whether a workbook has been saved. Reading is straightforward but setting is more interesting. Declaring
MyBook.Saved = True doesn’t actually save the workbook, it just tells Excel to act like it’s saved. So, if you don’t make any changes to the workbook and you go to close it, you won’t be prompted to save.
If you had asked me if it’s possible to set the Saved property on a never-been-saved workbook I would have said, “I don’t know, let me try it and get back to you.” Well you didn’t ask, but I’m getting back to you anyways. It turns out you can. You can open up a brand-new workbook, type “Movin’ to Montana soon” in A1 and “Gonna be a dental floss tycoon” in A2, then enter
Book1.Saved in the immediate window, and you won’t be prompted to save if you close it.
I didn’t expect that. It kind of makes sense that you can declare an existing workbook to be saved, but one that doesn’t even have a location yet? That doesn’t sit right with the left side of my brain, which would prefer that the property be re-named to something like
To expand on the above paragraph, a never-been-saved workbook doesn’t have a path. You can test the ActiveWorkbook with
ActiveWorkbook.Path = "", which returns True if the workbook has never been saved. I’d thought maybe setting Saved to True would change the Path property to the never-been-saved workbook location, but it doesn’t. Also, if you do decide to save, the Save As menu pops up, just as you’d hope. So it looks like setting Saved to True changes nothing except that you won’t be prompted to save.
I’ve found this useful for a certain kind of utility: one that prints out information which I want to examine but not keep. For instance, there’s a routine I use a lot that compares the sheets in two workbooks. It opens a new workbook and prints information about all the sheets in each book and whether they occur in the other book and, if so, the extent to which they match. The new workbook with the comparison is called “wbComparison,” so the last line of code is:
I know it’s a small thing, but it pleases me to look over the comparison and close it without being prompted to save.
Here’s a very simple sample for your testing pleasure:
Dim wbZappa As Excel.Workbook
Set wbZappa = Workbooks.Add
.Cells(1, 1) = "Movin' to Montana soon"
.Cells(2, 1) = "Gonna be a Dental Floss tycoon"
.Saved = True