Solved: FileSearch Compatability with Excel 2007

[Ischyros]

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
____________________________________________________

[lucas]

http://www.vbaexpress.com/forum/showthread.php?t=8092

http://www.vbaexpress.com/forum/showthread.php?t=22245

[Ischyros]

Thanks Lucas the scripting method should work! I spaced that out lol. Do you know how to mark a thread solved?

[lucas]

You can mark your thread solved using the thread tools at the top of the page.

[Ischyros]

Hi all, I recently posted on this same issue. I had a sub routine that maintained the number of files in a folder to a certain number. This routine involved the "FileSearch" method, which is no longer available in 2007. Lucas pointed me in the right direction for solving this problem which I did. I was hoping someone with more experience could look over my code and tell me if they see any way to improve it, or if that see any potential problems that I could encounter.

Essentially I find the earliest created file in the File Collection and delete it. I repeat this until the folder contains the most recent 15 files.
[vba]
Sub Folder_Maintenance()
Dim fso As Object, ObjFiles As Object, _
Folder As Object, f As Object, indFile, _
Dir As String
Dim FileName As String, MinDC As Double
Dir = "R:\FolderOne"
Set fso = CreateObject("Scripting.FileSystemObject")
Set Folder = fso.getfolder(Dir)
Set ObjFiles = Folder.Files

While ObjFiles.Count > 15
For Each indFile In ObjFiles
MinDC = indFile.datecreated
FileName = indFile.Name
'I use this for loop to get the first file's information since there is no
'index property for the FileSystemObject/Files Collection
Exit For
Next

For Each indFile In ObjFiles
If indFile.datecreated < MinDC Then
MinDC = indFile.datecreated
FileName = indFile.Name
Else
End If
Next

Set f = fso.GetFile(FileName)
f.Delete

Wend

End Sub
[/vba]

I know have been posting alot, but its been a busy week! Thanks in advance for you feedback!

[Kenneth Hobs]

I make it a rule to not use commands such as DIR as variable names.

Be sure to do your tests on copies of files in a backup folder. I am not sure that you would get the order by date right. It will probably work most times. I would have to do some testing to be sure.

I know that I can sort the files by date using a DOS Dir method as in one of the threads that Lucas referenced. To be sure the dates were in order, I would probably put all the filenames into an array with the date in yyyymmdd format prefixed to the filename. A sort would then get your top 15. Another method is to put your filenames and date created into a range and then sort and filter or sort to an array and then keep the first 15 and delete the others.

[lucas]

Ischyros,
I merged these two threads because you provide a solution to your question. It's better to keep them together so the train of thought is not broken, even if you have followup questions.

It also helps those with similar problems when a solution is found and demonstrated.

I hope you understand and agree with my action.

You can always post followup questions in the same thread even if you have marked it solved.