date file was last modified

[handysmurf]

OK ... I searched before posting ... and almost had it. I simply want to list the date a file was last modified in a spreadsheet. There are several files listed.

Here is the thread I found ... https://www.mrexcel.com/forum/excel...dified-date.html?highlight=date+last+modified

But ... I got nothing.... I'm not even sure I'm putting the code in the right place. In addition I have multiple worksheets in the workbook and I need to limit it to just one of them.

TIA

 

[Norie]

Do you have a list of files that you want to find the last date modified for?

If you do where is it located?

 

[handysmurf]

on a worksheet named "projects", the full file path for each file is listed in column B starting at row 4.

 

[Norie]

How are you trying to use the code in the post you linked to?

 

[handysmurf]

https://drive.google.com/open?id=14QGjOgfMAupuTwID04zHtQCTmxD9PiyZ

I also tried putting it the microsoft excel objects in the specific sheet

 

[alansidman]

Here is some code I have used in the past that you can modify to meet your needs.

Sub ListAllFile()


    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long


    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add


    'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"


    'Loop through the Files collection
    For Each objFile In objFolder.Files
    'If objFile.Name Like "*.pdf" Then
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
    'End If
    Next
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing


End Sub

 

[handysmurf]

OK ... as a wonderful display of my VBA prowess (NOT) ... the original script did nothing .. because .. I didn't RUN it. H3ll I don't know I guess I thought it would run on open or save or something. It only occurred to me after inserting Alan's script and guess what ... it does nothing until you RUN it. Sigh... So Alan's does work and it solves another problem for me as it gives a one click list of files where I had previously like run file list through the command line and cut and pasted ... bla bla long drawn out BS method that I actually don't even remember right now. Yes I will be using that in the future. But because the files are all in different directories it won't do I what I need at the moment.

So now back to the original ... when I do RUN it (eyes rolling) I get Run Time Error '53' File not found and on debug the following line is highlighted ... Set f = fso.GetFile(c.Value)

obviously I have no clue what the problem is or how to fix it.

 

[handysmurf]

Just for clarification since the original was in a link I'll post it here ....

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

the highlighted line is where the error is

 

[handysmurf]

Actually the code does work ... it returns the date ... so what is the error all about?