Displaying the date when a file was saved or printed

An Excel workbook contains several built-in document properties, accessible from the BuiltinDocumentProperties property of the Workbook object. The following function returns the date and time that the workbook was last saved:

Function LastSaved()

Application.Volatile LastSaved = ThisWorkbook. _

BuiltinDocumentProperties("Last Save Time") End Function

The following function is similar, but it returns the date and time when the workbook was last printed or previewed. If the workbook has never been printed or previewed, the function returns a #VALUE error.

Function LastPrinted()

Application.Volatile LastPrinted = ThisWorkbook. _

BuiltinDocumentProperties("Last Print Date") End Function

If you use these functions in a formula, you might need to force a recalculation (by pressing F9) to get the current values of these properties.

Note

Quite a few additional built-in properties are available, but Excel does not use all of them. For example, attempting to access the Number of Bytes property will generate an error. For a list of all built-in properties, consult the Help.

The preceding LastSaved and LastPrinted functions are designed to be stored in the workbook in which they are used. In some cases, you might want to store the function in a different workbook (for example, personal.xlsb ) or in an add-in. Because these functions reference ThisWorkbook , they will not work correctly. Following are more general-purpose versions of these functions. These functions use Application.Caller , which returns a Range object that represents the cell that calls the function. The use of Parent.Parent returns the workbook (that is, the parent of the parent of the Range object - a Workbook object). This topic is explained further in the next section .

Function LastSaved2 ()

Application.Volatile

LastSaved2 = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time") End Function

0 0

Post a comment