Get last modified date of all files in folder

KGee · Oct 1, 2010

I need to look at all files in a specific directory and see which of the files have a "DateLastModified" value which is equal to the current date. I then need to set a flag of some sort to let me know which files met the criteria so I know which files to look at. I know how to get the last modified date, but need help building the loop and setting the flag. I basically want to set a Y/N or T/F flag using a variable based on the file name, minus the extension. All of the files in the directory will be Excel files.

Domenic · Oct 2, 2010

The following macro will generate a list in your active sheet that includes the filename (including the full path), the date and time, and "Y" or "N", indicating whether the last modified date is equal to the current date.

Code:

Option Explicit

Sub test()

    Dim MyFolder As String
    Dim MyFile As String
    Dim NextRow As Long
    Dim MyDateTime As Date
    Dim MyDate As Date
    
    'Change the path, accordingly
    MyFolder = "C:\Users\Domenic\Desktop\"
    
    'Change the file filter (*.xlsx), accordingly
    MyFile = Dir(MyFolder & "*.xlsx")
    
    NextRow = 2
    Do While Len(MyFile) > 0
        MyDateTime = FileDateTime(MyFolder & MyFile)
        Cells(NextRow, "A").Value = MyFolder & MyFile
        Cells(NextRow, "B").Value = MyDateTime
        MyDate = Int(MyDateTime)
        If MyDate = Date Then
            Cells(NextRow, "C").Value = "Y"
        End If
        NextRow = NextRow + 1
        MyFile = Dir
    Loop
    
End Sub

KGee · Oct 4, 2010

Hi Domenic,

Thanks for your reply. This does the trick as requested but I'm not sure I'm taking the best approach to my problem.

We have a system where the user runs a report and the output goes to an Excel file. The output file has just the raw data with no formatting, field names, etc. so I created templates for each of the reports and macros to format each of the output files. I also created a user form with checkboxes to select the output files which need to be formatted. I want to have the checkboxes visible only for the output files that were created on the current date. Each checkbox would be linked to a macro that would format the report output using the corresponding template. I'm not sure how to get there from this point or if this method makes the most sense.

The output filenames are hard coded and do not change. The output files should always be present as they are never deleted but overwritten each time a new report is run. I suppose I could set variables to "N" for each of the report files and change to "Y" during processing if the date condition is met. I'm just not sure how to go about coding that either.

Get last modified date of all files in folder

KGee

Well-known Member

Excel Facts

Domenic

MrExcel MVP

KGee

Well-known Member

Similar threads

Forum statistics

Share this page

Get last modified date of all files in folder

KGee

Well-known Member

Excel Facts

Domenic

MrExcel MVP

KGee

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock