I have a list of file names with their full name (ie Path + Name) in column
A. 50 files total. Is there a macro or funtion that I can return the last
saved date for each file in column B and the time it was saved in column C?

Thank you for your help.

Steven

demo's from the immediate window:

dt = Filedatetime("C:\data\KZ081-Default Survey1.xls")
? cdate(clng(dt))
3/18/02
? dt-clng(dt)
6:08:40 AM

--
Regards,
Tom Ogilvy


"Steven" wrote in message
...
I have a list of file names with their full name (ie Path + Name) in
column
A. 50 files total. Is there a macro or funtion that I can return the
last
saved date for each file in column B and the time it was saved in column
C?

Thank you for your help.

Steven

One way:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim testStr As String
Dim myFileName As String
Dim myDate As Date

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
myFileName = .Cells(iRow, "A").Value
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

If testStr = "" Then
.Cells(iRow, "B").Value = "Not Found"
.Cells(iRow, "C").ClearContents
Else
myDate = FileDateTime(myFileName)
With .Cells(iRow, "B")
.NumberFormat = "mm/dd/yyyy"
.Value = Int(myDate)
End With

With .Cells(iRow, "C")
.NumberFormat = "hh:mm:ss"
.Value = myDate - Int(myDate)
End With

End If
Next iRow
End With

End Sub

Although, I think I'd keep the time and date in one cell. And just format it
pretty.

Steven wrote:

I have a list of file names with their full name (ie Path + Name) in column
A. 50 files total. Is there a macro or funtion that I can return the last
saved date for each file in column B and the time it was saved in column C?

Thank you for your help.

Steven

--

Dave Peterson

Dave:

Thank you for your help. That is a really nice macro. I was looking
through the Macro VB Help for how to get LastSavedBy but could not find
anything. Is there a way to get who saved the file last?

Thanks,

Steven

"Dave Peterson" wrote:

One way:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim testStr As String
Dim myFileName As String
Dim myDate As Date

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
myFileName = .Cells(iRow, "A").Value
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

If testStr = "" Then
.Cells(iRow, "B").Value = "Not Found"
.Cells(iRow, "C").ClearContents
Else
myDate = FileDateTime(myFileName)
With .Cells(iRow, "B")
.NumberFormat = "mm/dd/yyyy"
.Value = Int(myDate)
End With

With .Cells(iRow, "C")
.NumberFormat = "hh:mm:ss"
.Value = myDate - Int(myDate)
End With

End If
Next iRow
End With

End Sub

Although, I think I'd keep the time and date in one cell. And just format it
pretty.

Steven wrote:

I have a list of file names with their full name (ie Path + Name) in column
A. 50 files total. Is there a macro or funtion that I can return the last
saved date for each file in column B and the time it was saved in column C?

Thank you for your help.

Steven

--

Dave Peterson

? dt-clng(dt)
6:08:40 AM

should be

? dt-int(dt)
6:08:40 AM

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
demo's from the immediate window:

dt = Filedatetime("C:\data\KZ081-Default Survey1.xls")
? cdate(clng(dt))
3/18/02
? dt-clng(dt)
6:08:40 AM

--
Regards,
Tom Ogilvy


"Steven" wrote in message
...
I have a list of file names with their full name (ie Path + Name) in
column
A. 50 files total. Is there a macro or funtion that I can return the
last
saved date for each file in column B and the time it was saved in column
C?

Thank you for your help.

Steven

Are these Excel files (or MSOffice files)?

If yes, you can use the technique at Chip Pearson's site:
http://cpearson.com/excel/docprop.htm

Look for "Document Properties Of Closed Files"

and be careful. There's instructions for both version 1.2 and 2.0 of that DSO
dll.

Steven wrote:

Dave:

Thank you for your help. That is a really nice macro. I was looking
through the Macro VB Help for how to get LastSavedBy but could not find
anything. Is there a way to get who saved the file last?

Thanks,

Steven

"Dave Peterson" wrote:

One way:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim testStr As String
Dim myFileName As String
Dim myDate As Date

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
myFileName = .Cells(iRow, "A").Value
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

If testStr = "" Then
.Cells(iRow, "B").Value = "Not Found"
.Cells(iRow, "C").ClearContents
Else
myDate = FileDateTime(myFileName)
With .Cells(iRow, "B")
.NumberFormat = "mm/dd/yyyy"
.Value = Int(myDate)
End With

With .Cells(iRow, "C")
.NumberFormat = "hh:mm:ss"
.Value = myDate - Int(myDate)
End With

End If
Next iRow
End With

End Sub

Although, I think I'd keep the time and date in one cell. And just format it
pretty.

Steven wrote:

I have a list of file names with their full name (ie Path + Name) in column
A. 50 files total. Is there a macro or funtion that I can return the last
saved date for each file in column B and the time it was saved in column C?

Thank you for your help.

Steven

--

Dave Peterson


--

Dave Peterson