Application.FileSearch (2003)

Topic

New Reply

I have code that will zip cartain files in a directory. It looks like this.

    With Application.FileSearch
        .LookIn = "c:temp"
        .Filename = "*.xls"
        If .Execute(msoSortByLastModified, msoSortOrderAscending) > 0 Then
            TotFiles = .FoundFiles.Count
        End If
    End With
    
    For i = 1 To TotFiles
        Zip_Files (GetFilenameFromPath(Application.FileSearch.FoundFiles(i)))
        Pause 10
    Next i

It almost always works fine. I have other code to attach the resulting .zip files to an e-mail. It looks like this:

     With Application.FileSearch
        .LookIn = "c:temp"
        .Filename = "*.zip"
        If .Execute(msoSortByLastModified, msoSortOrderAscending) > 0 Then
            TotFiles = .FoundFiles.Count
        End If
    End With
    For i = 1 To TotFiles
        oMailItem.Attachments.Add Application.FileSearch.FoundFiles(i)
    Next i

More often than not, the second piece fails at this:

If .Execute(msoSortByLastModified, msoSortOrderAscending) > 0 Then

Am I missing something to make it work all the time? Is there a better way?

Reply | Quote

Replies

I think FileSearch can’t find .zip files because Windows XP sees them as (compressed) folders, not as files. You can replace the second part with

Dim strFile As String
Dim strPath As String
strPath = "C:Temp"
strFile = Dir(strPath & "*.zip")
Do While Not strFile = ""
oMailItem.Attachments.Add strPath & strFile
strFile = Dir
Loop

The only difference is that the zip files will be sorted by name, ascending – you can’t specifiy a different sort order using Dir.

Reply | Quote

Worked perfectly, thank you!!

Reply | Quote