Application.FileSearch does not find any files

ChrisA

Board Regular
Joined
May 23, 2002
Messages
50
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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Hi Chris

Try changing from filename
Code:
.Filename = "*.xls" 
.Execute
to filetype, something like this
Code:
.FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
'rest of the code
HTH

Regards
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Try adding in .NewSearch.
Code:
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

Well-known Member
Joined
Oct 14, 2003
Messages
1,487

ADVERTISEMENT

Chris

Try this and see what you get
Code:
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

Board Regular
Joined
May 23, 2002
Messages
50
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

Well-known Member
Joined
Oct 14, 2003
Messages
1,487

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

Board Regular
Joined
May 23, 2002
Messages
50
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,368
Messages
5,595,754
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top