Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Code to loop through and check last modified date

Thinking about trying to make something that will help keep a server clean and up to date.  I am looking for some VBA Code that will look through all the folder and files in a directory, and if the last modified date is greater than 7 days ago, I would like to list out the file path in an excel sheet.....

any thoughts?

-Brandon
0
KnutsonBM
Asked:
KnutsonBM
  • 2
3 Solutions
 
carsRSTCommented:
   Dim fileitem As File
    Dim fso
    Set fso = New Scripting.FileSystemObject
    Set SourceFolder = fso.GetFolder("c:\temp\")

    For Each fileitem In SourceFolder.Files
        If Right(fileitem.Path, 4) = ".xls" And Right(fileitem.Path, 12) <> "Comments.xlsx" Then
               
            If DateDiff("d", fileitem.DateLastModified, Now) > 7 Then
                 ActiveSheet.Cells(Range("A1").End(xlDown).Row, 1) = fileitem.Path
            End If
        End If
       
    Next
0
 
carsRSTCommented:
Sorry - took some code from a few places...didn't clean up properly.

Adjusted...


    Dim strPath As String
    strPath = "c:\temp\"
   
    Dim fileitem As File
    Dim fso
    Set fso = New Scripting.FileSystemObject
    Set SourceFolder = fso.GetFolder(strPath)

   
    For Each fileitem In SourceFolder.Files
        If Right(fileitem.Path, 4) = ".xls" Or Right(fileitem.Path, 5) = ".xlsx" Then

                Row = ActiveSheet.Range("A1").Range("A65536").End(xlUp).Row + 1
           
            If DateDiff("d", fileitem.DateLastModified, Now) > 7 Then
                 ActiveSheet.Cells(Row, 1) = fileitem.Path
            End If
        End If
       
    Next
0
 
Rob HensonIT & Database AssistantCommented:
There is a good old fashioned DOS routine that you can run that will extract contents of a directory and its sub-directories and export them to a text file.

The parameters that can be added to the statement include size and filetype so no doubt they can include modified date.

It is based on the DIR command but I cannot remember the exact parameters.

I will start investigating.

Thanks
Rob H
0
 
Bill PrewCommented:
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now