[RESOLVED] Excel VBA - Show Last Saved/Modified Date

[Hack]

I need to display the date and time a spreadsheet was last saved each time the sheet is opened. I use the following function and incorporate it as shown

Public Function myDate()
  myDate = Format(FileDateTime(Application.ActiveWorkbook.FullName), "mm/dd/yyyy h:n ampm")
End Function

Private Sub Workbook_Open()
Sheet1.Range("D38") = myDate
End Sub

The problem is that when the file is opened is displayes the current date and time not the last time the file was saved.

How would I fix this?

I've done a fair amount of Googling about as well as looking through the Excel VBA code here on VBF and the general consensus seems to be that using FileDateTime is the way to go....but it doesn't want to play the way Uncle Hack needs it to play.

[BillBoeBaggins]

?excel.Application.ThisWorkbook.BuiltinDocumentProperties.count
 30 
?excel.Application.ThisWorkbook.BuiltinDocumentProperties("Last Author")
Sutter Connect
?excel.Application.ThisWorkbook.BuiltinDocumentProperties("Creation Date")
10/14/1996 4:33:28 PM 
?excel.Application.ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
11/29/2005 1:10:27 PM

http://msdn.microsoft.com/en-us/libr...es(VS.80).aspx

[Hack]

BuiltinDocumentProperties -

Didn't see that comin'

Thank you sir!

[BillBoeBaggins]

I was thinking there HAS to be something in the Excel Properties with this so I just started cycling through the properties and hit gold with BuiltInDocumentProperties. heh.

[dejayajay]

Hi 'Hack'. Can you show me how BillBooe's code fits into your suggested code, assuming that it does. As you probably can tell, I am a bit of a novice with VB and coding. Like you, I want the last saved date to remain 'intact' upon next opening.