Get last modified date

[apinel]

I have an excel spreadsheet with multiple file names (including file path) listed in a column. I have to update other files using these 'underlying' files. So each cell has the file path and file name (i.e. Q:\Folder\File Name.xls). I would like to have the adjacent cells indicate the last modified date for each file. This saves me time from looking for the last modified date manually across different folders in my servers to see if the 'underlying' file has been updated, which I would need in order to update my other files. Help is greatly appreciated.

So this function would be something like "get last modified date based on file path". I will need step by step instructions as I am not very familiar with VBA. Thanks!

Example:

A B
1 Q:\Folder1\File name1.xls 2/10/2012
2 X:\Folder2\File name2.xls 12/1/2011

etc

 

[hiker95]

apinel,

See if this will get you started in the right direction:

http://www.mrexcel.com/forum/showthread.php?t=373373

 

[apinel]

apinel,

See if this will get you started in the right direction:

http://www.mrexcel.com/forum/showthread.php?t=373373

Thank you for the link!
The code works fine. However it is not too efficient if you have a couple of hundred files that I need to check the last modified date. Is it possible to reference the cell instead of the actual file path, in the code? Otherwise I have to manually input the file path in the code for each file. For example, the code calls for the following line:

Set f = fso.GetFile("A:\File.ext")
Range("L3").Formula = f.DateLastModified
Range("L3").Select

Is it possible to do something like (to reference A1 in the worksheet).

Set f = fso.GetFile("A1")
Range("L3").Formula = f.DateLastModified
Range("L3").Select

I tried this but got an error message saying file not found.

Also, I have another list of files on the same workbook but on a different worksheet. Is it possible to have the same code on different worksheets? Or I guess another way of saying is it possible to write a separate macro to apply to another worksheet in the same workbook?

Help is greatly appreciated.

 

[JoeMo]

This assumes your files are listed in column B starting in B1, change column to suit. Date file was last modified will be returned in column C:

Sub GetLastDateModified()
    '**************************************************
    'Column B  of the active sheet should contain full file paths and file names
    'with file extension info
    '**************************************************

    Dim fso, f, lRw As Long
    lRw = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each c In Range("B1", "B" & lRw)
        If Not IsEmpty(c) And InStr(1, c.Value, "\") > 0 Then
            Set f = fso.GetFile(c.Value)
            c.Offset(0, 1).Formula = f.DateLastModified
        End If
    Next c
End Sub

 

[apinel]

This assumes your files are listed in column B starting in B1, change column to suit. Date file was last modified will be returned in column C:

Sub GetLastDateModified()
    '**************************************************
    'Column B  of the active sheet should contain full file paths and file names
    'with file extension info
    '**************************************************

    Dim fso, f, lRw As Long
    lRw = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each c In Range("B1", "B" & lRw)
        If Not IsEmpty(c) And InStr(1, c.Value, "\") > 0 Then
            Set f = fso.GetFile(c.Value)
            c.Offset(0, 1).Formula = f.DateLastModified
        End If
    Next c
End Sub

This works great! Thanks!