Fix for FileSearch in Office 2007

[ginismo]

When I noticed that FileSearch was missing after I updated to Office 2007, I decided to recreate the class and save it as a file to import anytime I needed to use it. I tried to use as little code as possible, so if there is some property you would need to add then you may have to add it yourself, but this will at least give you most of the funcitonality of the original Class without having to update your existing code too much. You have to just reference the class and then it can still be used in a with block or however you are accustomed. Here are the two class files that I have, the first I named FileSearch (go figure):

Dim pLookIn As String
Dim pSearchSubFolders As Boolean
Dim pFileName As String
Public FoundFiles As New Collection
        
Public Property Get LookIn() As String
    LookIn = pLookIn
End Property
Public Property Let LookIn(value As String)
    pLookIn = value
End Property
Public Property Get SearchSubFolders() As Boolean
    LookIn = pSearchSubFolders
End Property
Public Property Let SearchSubFolders(value As Boolean)
    pSearchSubFolders = value
End Property
Public Property Get fileName() As String
    fileName = pFileName
End Property
Public Property Let fileName(value As String)
    pFileName = value
End Property
Public Function Execute() As Long
    
        Dim ex As Long
        Dim sLookIn As String
        Dim sDirName As String
        Dim sSubDir As String
        Dim sFileName As String
        Dim ff As FilesFound
    
            Set ff = New FilesFound
            sLookIn = LookIn
            sDirName = Dir(sLookIn, vbDirectory)
            sFileName = Dir(sLookIn & "\", vbNormal)
            Do Until Len(sFileName) = 0
                If sFileName Like fileName Then
                    ff.AddFile sLookIn, sFileName
                    FoundFiles.Add (ff.FoundFileFullName)
                End If
                sFileName = Dir
            Loop
            If SearchSubFolders Then
                Do Until Len(sDirName) = 0
                    If GetAttr(sLookIn & sDirName) = vbDirectory Then
                        sSubDir = sDirName
                        Do Until Len(sFileName) = 0
                            If GetAttr(sDirName) = vbNormal Then
                                sFileName = sDirName
                                ff.AddFile sDirName, sFileName
                                FoundFiles.Add (ff)
                            End If
                        Loop
                    End If
                    sDirName = Dir
                Loop
            End If
    
    Execute = FoundFiles.Count
    
End Function
The second I named FilesFound and the code is as follows:
Public FoundFileFullName As String
Public Function AddFile(path As String, fileName As String)
    FoundFileFullName = path & "\" & fileName
End Function

That should do it.
To use it in your code just create the two class modules and then use it as such:
    Dim sFile as String
    Dim fs As New FileSearh
    With fs
        .LookIn = sPath
        .SearchSubFolders = True
        .fileName = "*"
        If .Execute > 0 Then
            For i = 1 To .FoundFiles.Count
                sFile = .FoundFiles(i)
                    ' your code here
            Next
        End If
    End With

so it works exactly the same. The only difference I know of is, if the file you're looking for may have any extension use "*" instead of "*.*" as microsoft put out. I was lazy and just did a like comparison instead of breaking out the file extensions. All others should theoretically work fine i.e. "*.xls" or "*.txt"
That's it. Enjoy!!!!!!!!

James

 

[ginismo]

Quick reply!

The first link is very similar to what I have found. I may have to play with it and see what I can get from it. I wasn't able to yesterday, because there were reports that needed to be made that used that code, and I was under time constraints. Maybe today I can play with this a little. I would still like to create a .dll from .Net. Maybe both. I just think that there should be a "free" fix for this for everyone to use who has been forced to upgrade to 2007. I've seen fixes, but they are being sold for over $100, which I think is very oppurtunistic.

 

[tusharm]

For two modularized approaches that use Dir, see my
Process all files in a folder and, optionally, in sub-folders
http://www.tushar-mehta.com/publish_train/xl_vba_cases/process_all_files_in_folder.htm
and Karl Peterson's http://vb.mvps.org/samples/project.asp?id=DirDrill

 

[Kenneth Hobson]

I could not get your routine to work properly Tushar. It kept showing error 52. Even pressing OK until it was done did not find all files. I searched a pen drive so that might make a difference in your routine.

Karl doesn't want us to post any part of his code so I won't be using it.

I need to investigate why my DOS routine finds more files than Application.FileSearch and recursive fso searching methods.

 

[tusharm]

Must be since it works just fine with a disk directory.

I could not get your routine to work properly Tushar. It kept showing error 52. Even pressing OK until it was done did not find all files. I searched a pen drive so that might make a difference in your routine.

Karl doesn't want us to post any part of his code so I won't be using it.

I need to investigate why my DOS routine finds more files than Application.FileSearch and recursive fso searching methods.

 

[Kenneth Hobson]

I forgot, X: is my mapped drive on my local c:\myfiles\excel. Even when I set it to the actual path, it only does the root folder.

No sense repeating this or your code but here is part of it though it is mostly just what you did. Guess I need to F8 through it and see what might cause it not to iterate the subfolders of c:\myfiles\excel.

Sub ListAllFiles()
    searchForFiles "c:\myfiles\excel\", "pOneFile", "*.*", True
End Sub

Sub pOneFile(ByVal aFilename As String)
    Debug.Print aFilename
    'Range("A" & Rows.Count).End(xlUp).offset(1).value = aFilename
End Sub