PDA

View Full Version : Solved: Find files in folder that have not been saved in x days



slang
06-06-2011, 04:24 AM
I am looking for some starting code so I can look in a specific location for files that have not been saved in 7 days so I can output them in to a list.
What property would I be looking for?

Been a while and a bit :think: rusty for sure.

Thanks anyone for a starting point to get my brain in gear .....

youngmcc
06-06-2011, 06:07 AM
I am looking for some starting code so I can look in a specific location for files that have not been saved in 7 days so I can output them in to a list.
What property would I be looking for?

Been a while and a bit :think: rusty for sure.

Thanks anyone for a starting point to get my brain in gear .....

Hi,

I'm only starting to get involved with the forum after relying on it so much myself so go easy on me.

Heres my solution:



Sub test()
Dim fso As New FileSystemObject
Dim fls As Files
Dim ModDate As Date
Dim strText As String
Dim i As Integer

FolderPath = "I:\" 'Change to your file path

Set fls = fso.GetFolder(FolderPath).Files

i = 2

ModDate = Date - 7 'ModDate = Today minus the required number of days

With Worksheets("Sheet1")

.Cells(1, 1) = "FileName"
.Cells(1, 2) = "File Size"
.Cells(1, 3) = "Date"

For Each f In fls

If f.DateLastModified < ModDate Then

.Cells(i, 1) = f.Name
.Cells(i, 2) = f.DateLastModified

i = i + 1

Else

End If
Next

End With

End Sub

youngmcc
06-06-2011, 08:32 AM
PS - I should note that you need to add the Scripting Runtime reference for this to work.

In the VB Editor (Alt and F11) go to

Tools > References

Check Microsoft Scripting Runtime

Press Ok

slang
06-06-2011, 09:09 AM
I defined the "folderpath as a string but I am getting a compile error in the
For Each F in fls
If I define it I get another error in the next statement
if f.Datelastmodified

Sorry, bit rusty i guess...

Kenneth Hobs
06-06-2011, 09:59 AM
Most likely, you did not set the reference in Tools > References...

I used late binding in this tweak so that you don't have to set a reference. It assumes that FolderPath exits. It is easy enough to check in code though.

Sub test()
Dim fso As Object
Dim fls As Object
Dim f As Object
Dim ModDate As Double
Dim strText As String
Dim i As Integer
Dim FolderPath As String

FolderPath = "x:\t\" 'Change to your file path
Set fso = CreateObject("Scripting.FileSystemObject")
Set fls = fso.GetFolder(FolderPath).Files
i = 2
ModDate = Date - 7 'ModDate = Today minus the required number of days

With Worksheets("Sheet1")
.Cells(1, 1) = "FileName"
.Cells(1, 2) = "File Size"
.Cells(1, 3) = "Date"
For Each f In fls
If f.DateLastModified < ModDate Then
.Cells(i, 1) = f.Name
.Cells(i, 2) = f.Size
.Cells(i, 3) = f.DateLastModified
i = i + 1
Else
End If
Next
End With

Columns("A:C").AutoFit

Set fls = Nothing
Set fso = Nothing
End Sub

slang
06-07-2011, 03:39 AM
Awesome! :friends:
I am not in VBA as much as I used to and am getting rusty on even the most simple aspects of it. Time to get back in the know again as I have a few small projects coming up.

THANKS again, I forgot all about the .cell(X,X) method.
Rusty for sure :dunno

youngmcc
06-07-2011, 06:03 AM
Awesome! :friends:
I am not in VBA as much as I used to and am getting rusty on even the most simple aspects of it. Time to get back in the know again as I have a few small projects coming up.

THANKS again, I forgot all about the .cell(X,X) method.
Rusty for sure :dunno

Glad to have contributed :)
Keep your projects coming! :hi: