LastModified Date of Excel file

[R Douglas]

Hi all,

The function FileDateTime returns "the date and time when a file was created
or last modified." But it doesn't seem to work (the way I thought it would)
when the file is an open XLS workbook.

I would like to put in a cell, the date of my excel file was last modified
and last saved to disk. I thought that this would do it:

Sub TestDate()
fileSpec = ThisWorkbook.FullName
Dim myDate As String
myDate = FileDateTime(fileSpec)
Range("A1").Value = myDate
End Sub

But this gets the date and time the file was opened. How do I get the date
when the file was saved to disk (like the "Last Modified" date in Windows
Explorer)?

I've also tried
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(fileSpec)
Range("A2").Value = fileSpec + " " + Str(f.DateLastModified)
Range("A3").Value = fileSpec + " " + Str(f.DateLastAccessed)
Range("A4").Value = fileSpec + " " + Str(f.DateCreated)
but DateLastModified and DateLastAccessed are always the same - the date the
file was opened.

Thanks in advance,

 

[Greg Koppel]

An example

Sub ShowInProcessActive()
Dim fs, f, f1, fc, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("P:\Data\ipopen\")
Set sf = f.subfolders
For Each f1 In sf
If Left(f1.datelastmodified, Len(Date)) = Str(Date) Then
s = s & f1.Name & vbTab & f1.datelastmodified
s = s & vbCrLf & vbCrLf
End If
Next
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("I:\Projects\arms\in process\")
Set sf = f.files
For Each f1 In sf
If Left(f1.datelastmodified, Len(Date)) = Str(Date - 1) Then
s = s & f1.Name & vbTab & f1.datelastmodified
s = s & vbCrLf & vbCrLf
End If
Next
MsgBox s, , "In-Process folders modified today"
End Sub


HTH, Greg

 

[R Douglas]

Thanks Greg,

Datelastmodified doesn't seem to work with its own excel file. It does not
give the date and time the file was last modified. It gives the current
time:

Sub TestDate()
fileSpec = ThisWorkbook.FullName
Set fs = CreateObject("Scripting.FileSystemObject")
Set f1 = fs.GetFile(fileSpec)
MsgBox (f1.datelastmodified) '<<GIVES CURRENT DATE & TIME, NOT LAST
MODIFIED DATE
End Sub

Maybe there is no way to get the last modified time of an open excel file.

Roger