Look up file names and last modified

[ShawnR]

Hi,

I have been able to pull up file names under a folder, but having a hard time trying to to pull up last modified dates for the corresponding files as well.

Looking for Non VBA ways to do so first so that I can understand it better. As I am not well versed with VBA.

Help would be great!

 

[alansidman]

I don't know of a way to do this without VBA

Here is a code and instructions on how to use it. Put it in a new workbook.

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

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

The only other solution is to open each file, click on File and look for the last modified date in the right hand column. A very tedious task if you have many files to examine.

 

[ShawnR]

Thank you so much for that! I have been working on this for some time this was so helpful!

 

[alansidman]

You are welcome