date file was last modified


Board Regular
Jan 17, 2015
Office Version
  1. 365
  1. Windows
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 ...

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.


Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Do you have a list of files that you want to find the last date modified for?

If you do where is it located?
Upvote 0
on a worksheet named "projects", the full file path for each file is listed in column B starting at row 4.
Upvote 0
How are you trying to use the code in the post you linked to?
Upvote 0
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
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub
Upvote 0
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.
Last edited:
Upvote 0
Just for clarification since the original was in a link I'll post it here ....

Rich (BB code):
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
Last edited:
Upvote 0
Actually the code does work ... it returns the date ... so what is the error all about?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back