Application.FileSearch does not find any files

[ChrisA]

Hi,

I am trying to use Application.FileSearch to find all Excel files in a certain folder. For some reason no files are found although there are definitely Excel files in this folder. Here is my code:

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Documents and Settings\Chris\My Documents\Files Not Scanned"
.Filename = "*.xls"
.Execute
myfiles = .FoundFiles.Count
End With

Any ideas?

 

[Glaswegian]

Hi Chris

Try changing from filename

.Filename = "*.xls" 
.Execute

to filetype, something like this

.FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
'rest of the code

HTH

Regards

 

[ChrisA]

This still returns zero files. Any other ideas?

 

[Norie]

Try adding in .NewSearch.

Set fs = Application.FileSearch 
With fs
     .NewSearch 
     .LookIn = "C:\Documents and Settings\Chris\My Documents\Files Not Scanned\" 
     .Filename = "*.xls" 
     .Execute 
     myfiles = .FoundFiles.Count 
End With

 

[Glaswegian]

Chris

Try this and see what you get

ub DetectFiles()
Dim x As Integer
Dim myPath
Dim strReply

myPath = "C:\Documents and Settings\Chris\My Documents\Files Not Scanned"

With Application.FileSearch
    .LookIn = myPath
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
    MsgBox "We found " & .FoundFiles.Count & " files."
        For x = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(x)
        Next x
            Else
                MsgBox "Sorry, no files found"
    End If
End With
End Sub

I’ve used this OK so it should work for you.

Regards

 

[ChrisA]

Thanks for both of your suggestions, but I still get 0 files found. I can open the filie if I name it explicitly:

Workbooks.Open Filename:= _
"C:\Documents and Settings\Chris\My Documents\Files Not Scanned\FileNumNotScanned_1279calonso1.xls"


But using filesearch does not work. Any other ideas?

 

[Glaswegian]

Chris

Is the file path correct? I checked again and my code finds all the Excel files in a specified directory - check spelling, spaces etc.

Regards

 

[Tom Urtis]

Some versions are picky about FileSearch, for example Excel97 errors when a wildcard is used such as you are doing, though you are probably not using 97.

Try the below macro just as I posted it and please note:

- The " \ " at the end of the path string declaration
- A preliminary close-ended If structure to verify existence of path
- This line:
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
which sometimes helps.



Sub Test1()
Dim i%, MyPath$
MyPath = "C:\Documents and Settings\Chris\My Documents\Files Not Scanned\"

If Len(Dir(MyPath, vbDirectory)) = 0 Then
MsgBox "No valid path exists named " & MyPath & ".", 48, "Cancelled."
Exit Sub
End If

With Application.FileSearch
.LookIn = MyPath
.FileName = "*.xls"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "We found " & .FoundFiles.Count & " files."
For i = 1 To .FoundFiles.Count

MsgBox (.FoundFiles(i))

Next i
Else
MsgBox "There were no files found in " & MyPath & ".", 48, "Cannot continue"
End If
End With
End Sub




Let us know if this is still not working. If not, something else is going on with your workbook, maybe your macro settings are too stringent or your events are disabled, who knows, because at least one of these suggested macros should be working for you.

 

[ChrisA]

HI Tom,

Your code returns the message, "There were no files found...". I am using Excel 2002. I'm not sure how to make the macro settings less stringent or enable events. Do you have any suggestions? Thanks.

 

[Tom Urtis]

OK, I am positive that code works, and I tested the other macros you received and they work too. We know version 2002 is not an issue. Events are not an issue (you would not have received a message box if it were). It is not the settings either.

Take a moment to do something that sounds odd, even though you said you already did...go into Windows Explorer and make absolutely certain that you have files in that path with the .xls extension...not .xla or .txt or anything else, just confirm they really are .xls. I cannot think of any logical reason why this is happening to you.

By the way, what is the event you are calling this macro with? Clicked from a Forms button? Command Button (if so set the TakeFocusOnClick property to False)?, or a worksheet level event, or what?