Ischyros
12-30-2008, 04:29 PM
I use the code below to keep the amount of files in a folder to less then 15....I know that the "FileSearch" Method has been removed from Excel 2007 but I want to make this code work for 2007. With that said the macros fails as at the line of code " Set fso = Application.FileSearch"
Please Help!
___________________________________________________
Function CountFiles() As Double
'Function purpose: To count all files in a directory
Dim fso As Object, _
objFiles As Object
Dim Directory As String
Dim FileKill As String
Directory = "R:\Capital Projects\Status Reports\Project Resource Management Form\Log Files"
'Create objects to get a count of files in the directory
Set fso = Application.FileSearch
CountFiles = 20
While CountFiles > 15
On Error GoTo ErrHandler
With fso
'Set the directory to look in to the directory arguement supplied by the user
.LookIn = Directory
'Determine the msoFileType group that the file extension belongs to.
.FileType = msoFileTypeAllFiles
'Execute the search
.Execute (msoSortByLastModified)
CountFiles = .FoundFiles.Count
'MsgBox (CountFiles)
FileKill = .FoundFiles.Item(1)
'MsgBox (FileKill)
If CountFiles > 15 Then
'MsgBox (FileKill)
SetAttr FileKill, vbNormal
Kill FileKill
Else
End If
End With
Wend
ErrHandler:
End Function
____________________________________________________
Please Help!
___________________________________________________
Function CountFiles() As Double
'Function purpose: To count all files in a directory
Dim fso As Object, _
objFiles As Object
Dim Directory As String
Dim FileKill As String
Directory = "R:\Capital Projects\Status Reports\Project Resource Management Form\Log Files"
'Create objects to get a count of files in the directory
Set fso = Application.FileSearch
CountFiles = 20
While CountFiles > 15
On Error GoTo ErrHandler
With fso
'Set the directory to look in to the directory arguement supplied by the user
.LookIn = Directory
'Determine the msoFileType group that the file extension belongs to.
.FileType = msoFileTypeAllFiles
'Execute the search
.Execute (msoSortByLastModified)
CountFiles = .FoundFiles.Count
'MsgBox (CountFiles)
FileKill = .FoundFiles.Item(1)
'MsgBox (FileKill)
If CountFiles > 15 Then
'MsgBox (FileKill)
SetAttr FileKill, vbNormal
Kill FileKill
Else
End If
End With
Wend
ErrHandler:
End Function
____________________________________________________