Problems with Excel returning .FoundFiles = 0

[pcfuqua]

In the code below (and in several previous variations of) I keep getting a value of 0 for .Execute and .FoundFiles. I have had several experiences where it has correctly returned the number of files in the folder one day, and zero the next, even though there have always been files in the folder. The code returns the correct folder name so I know it is searching in the correct location, it just doesn't see the files that I know are there. Why don't I consistently get the correct number of files returned?

Sub Execute_Table_Load()

Dim File_Path As String
Dim Folder_Path As String
Dim i as Integer
Dim Document_List As String
Dim docName as String
Dim fs As FileSearch

File_Path = ThisWorkbook.Path

'Build a path to the folder where the word documents are:
Folder_Path = File_Path & "\Test"

Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = Folder_Path
.SearchSubFolders = False
.FileType = msoFileTypeWordDocuments

If .Execute > 0 Then

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True

For i = 1 To .FoundFiles.Count

Set wrdDoc = wrdApp.Documents.Open(.FoundFiles(i))
docName = Left(wrdDoc.Name, Len(wrdDoc.Name) - 4)
'rest of code goes here
Document_List = Document_List & vbNewLine & docName

Next i

MsgBox "Data from these files were extracted:" & vbNewLine & Document_List
wrdApp.Quit 'close the Word application

Else

MsgBox "No Documents were found in folder: " & Folder_Path

End If

Set wrdApp = Nothing 'Explicitly clear memory

End With

End Sub

 

[BrianB]

Your code would be easier to read if it was properly formatted, commented, and displayed on the board as code. It is just short enough for me to bother deciphering it. No-one else it seems.

I can see nothing wrong with it (The FileSearch bit).
The only things I can think of is ...
1. If ThisWorkbook is not saved it has no path.
2. The macro may be corrupted. Copy/Paste to NotePad then from there to a clean module.
3. Otherwise the symptoms still suggests a path error. Need to treble check what you are getting.
4. Try .FileType = msoFileTypeAllFiles


Otherwise ... I suggest you strip it down to just the FileSearch bit with hard coded strings to see what happens. Could be an Excel problem I suppose.

 

[pcfuqua]

Thanks for looking Brian,

I still can't get it working. I created a new Excel file, pasted the following code into it and saved the file to the desktop. I also placed a folder named Word with 6 .doc files on my desktop. I even replaced the ThisWorkbook.Path in the code below with the actual folder string and tried it. No matter what I do, I keep getting .FoundFiles = 0. I'm not sure if there is a Tools/References selection besides the MS Word 11.0 Object Library that I need. I'm really stumped since this has worked before. Thanks again for helping. This was my first post and I didn't realize I needed to bracket my code with a special character. Paul

Sub Find_Files()

Dim File_Path, Folder_Path As String
Dim fs As Object

    File_Path = ThisWorkbook.Path
    Folder_Path = File_Path & "\Word"
    Set fs = Application.FileSearch
    
    With fs
    .NewSearch
    .LookIn = Folder_Path
    .SearchSubFolders = True
    .FileType = msoFileTypeWordDocuments
        If .Execute > 0 Then
            MsgBox .FoundFiles.Count & " Documents were found in folder: "_
            & Folder_Path
        Else
            MsgBox "No Documents were found in folder: " & Folder_Path
        End If
    End With
    
End Sub

 

[Kenneth Hobson]

Most likely, your ThisWorkbook's path is not what you think it is. Use DIR to check that the folder exists before FileSearch.

MsgBox Folder_Path & vbCrLf & Dir(Folder_Path, vbDirectory)

I use this to get the desktop folder.

Sub ShowDesktopFolderPath()
  MsgBox DesktopFolder
End Sub

Function DesktopFolder()
  Dim wshShell As Object
  Set wshShell = CreateObject("WScript.Shell")
  DesktopFolder = wshShell.specialfolders("Desktop")
End Function

 

[pcfuqua]

Thanks for the suggestion, but I'm still stumped. I ran the script below with an existing and bogus folder folder name. The script returns the existing folder name when I use the existing folder and nothing when I use the bogus folder. The folder exists, and VBA sees it, but it just can't see the files in the folder. Also, when I use msoFileTypeAllFiles, Excel crashes. Not sure what's going on here.

 

[BrianB]

There is an error in this bit of code. You have left out the space in front of the underscore. So you should not have been able to run the routine without an error message.

            ' This code contains an error
            MsgBox .FoundFiles.Count & " Documents were found in folder: "_
            & Folder_Path

FWIW I copy pasted your latest code and it ran with no problem on my machine after the correction.

Another thought .. check out that you have proper access to the folders & their properties.

 

[RoryA]

Have you tried specifying the filename using wildcards? (I assume the files are not hidden)

 

[Kenneth Hobson]

I guess it is a stumper since we can't reproduce the problem.

Could be a permission or file attribute issue as Brian said.

If you are going to v2007, then you can't use FileSearch. One can still use DIR, filescripting methods and even DOS methods though. I have found the DOS method better than FileSearch for some root folder searches.

 

[pcfuqua]

I discovered that when the excel file and folder containing the word files are on my desktop, the code returns the value correctly using ThisWorkbook.Path. If I create a folder on my desktop and move the excel file and folder containing the word files to the newly created folder (basically just nesting one level from the desktop), the code does not work. It tells me that it searched the correct location, but for some reason my folders are doing something to block the macro from seeing what is inside them. Any ideas. I've tried playing with the folder properties, but nothing seems to work.

Thanks again for everyone's help with this one.

 

[pcfuqua]

Another thing I found is that when I create folders, they have a square in the read only check box. When I remove the square, apply, then click OK, the square will return when I later open the folder properties again.