Application.FileSearch 1

[MikeC14081972]

HI,

Been using an old version of Excel and use the following code to loop through the files in a folder and process them in the order they were last modified.

Wtih Application.FileSearch
    .NewSearch
    .LookIn = "C:\Test\"
    .FileType = msoFileTypeAllFiles
    If .Execute(msoSortByLastModified, msoSortOrderAscending) >0 then
        ' Do Stuff
    End If
End With

Now I have updated to Excel 2007 the line

 With Application.FileSearch

throws an error as this has been removed from 2007

I know I could build code using FSO but want to be able to process any files in the order they were last modified still.

Can anyone help me out.

Thanks

 

[HarleyQuinn]

I almost forgot that I wrote something a while ago that has some of the functionality of FileSearch.

Here's the post with details:

http://www.tek-tips.com/viewthread.cfm?qid=1554897

If Mr Strong or Gerry are reading this, no it's not finished (the quick reason is that I forgot, the longer version is that I've been busy and not had time to finish it yet)

Hope this helps

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[fumei]

Mike, HQ's replacement of FileSearch is (while not "finished") is a reasonably good facimile for FileSearch. It may work for you. Get a copy and try it to see.

Other than that, and trying to get your own reasonable facimile using FSO, you are SOL.

And a "wag of the finger" - again - to Microsoft for removing it.

Gerry

 

[MikeC14081972]

Cheers guys.

As a quick and dirty solution I've passed the filename and datelastmodified into a hidden sheet and I'm then sorting on the modified date column then reading the filenames back in in the required order.

Might look at passing to an array and then sorting that somehow. Can this be done on a 2 column array like

1.txt;3/18/2010 16:00|2.txt;3/18/2010 15:38| etc etc

so 2.txt would be processed before 1.txt????

 

[MikeC14081972]

fumei - SOL?????

 

[MikeC14081972]

Fumei I'm **** out of luck? lol

Anyway this is the solution I came up with if anyone else needs to do this.

Function SortFiles(fldPath As String)

Dim arrFiles As Variant
Dim Temp    As Variant
Dim i   As Integer
Dim fso As FileSystemObject
Dim fld As Folder
Dim fle As File
Dim FleCount As Integer

Set fso = New FileSystemObject
Set fld = fso.GetFolder(fldPath)

ReDim arrFiles(2, 1)

For Each fle In fld.Files
    FleCount = FleCount + 1
    ReDim Preserve arrFiles(2, FleCount)
    arrFiles(1, FleCount) = fle.Name
    arrFiles(2, FleCount) = fle.DateLastModified
Next fle


If FleCount > 0 Then
    For i = 1 To UBound(arrFiles, 2)
        For j = i + 1 To UBound(arrFiles, 2)
            If (arrFiles(2, j) < arrFiles(2, i)) Then
                Temp = arrFiles(1, i)
                arrFiles(1, i) = arrFiles(1, j)
                arrFiles(1, j) = Temp
                Temp = arrFiles(2, i)
                arrFiles(2, i) = arrFiles(2, j)
                arrFiles(2, j) = Temp
            End If
       Next j
    Next i
    
    For i = 1 To UBound(arrFiles, 2)
        ProcessFile USfPath & arrFiles(1, i)
    Next i
End If

End Function

 

[fumei]

Just out of curiosity...

You have a function:

Function SortFiles(fldPath As String)

but never actually use the function name, or set its value.


Why use a Function? It seems to me to be a Sub.

Gerry

 

[MikeC14081972]

Sorry for the late reply, you are correct a Sub would have been better only that wasn't the final finished code.

I just built that bit first to get the issue I was having resolved.