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,

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" wrote in message
...
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 t
he
file was opened.

Thanks in advance,

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

"Greg Koppel" wrote in message
...
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" wrote in message
...
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
t
he
file was opened.

Thanks in advance,