Determine if an Excel workbook has ever been saved?

Question

How can I tell, using VBA, if an Excel workbook has ever been saved?

I want to know if clicking save will save the document to an existing location or prompt me to choose a save location.

Answer 1

The name (e.g. Book1) is a clue although I suppose someon could simply save their workbook with that name.

A clearer check may be as simple as a worksheet function that only works on a saved workbook.

'the following returns an empty string if used on an unsaved workbook
=CELL("filename", A1)

In VBA the .Address property will return no path when used with the External:=True argument.

?range("A1").Address(external:=true)
[Book1]Sheet5!$A$1

Answer 2

I found this helpful. .BuiltinDocumentProperties("last save time") returns a vbDate (VarType = 7) representing the last time the workbook was saved ONLY IF the file has been saved at least once. Otherwise it returns a vbObject (VarType = 9).

Function WbSavedAtLeastOnce(ByVal target As Workbook) As Boolean
' Returns TRUE if the target workbook has been saved at least once.

    ' .BuiltinDocumentProperties("last save time") returns a vbDate
    '   only if the file has been saved at least once.
    WbSavedAtLeastOnce = VarType( _
            target.BuiltinDocumentProperties("last save time")) = 7
End Function

Use the function like this:

Sub Test()
    If WasSavedAtLeastOnce(ActiveWorkbook) Then
        MsgBox "This file has been saved at least once."
    Else
        MsgBox "This file has never been saved."
    End If
End Sub

Answer 3

There is another method - IF you have programmatic access to the VBA Project (a trust center setting).

(from the debug window)

for each n in thisworkbook.VBProject.VBComponents : ?n.name, n.type, n.saved : next

Look at ThisWorkbook's .saved property value.

false = workbook was never saved.