FileSearch VBA error - please help!

[Kenneth Hobs]

Where the code is located depends on the kind of command button, forms or activex control. Posting a short sample workbook would help me help you better. You can manually add the new sheet with the output expected.

In any case, one typically puts Functions into a Module.

Why don't you test the code as I suggested to see if it will get what you need? Putting the result in column A of a new sheet is trivial. I can show that to you later.

Put all of this in a Module. Then run the Test sub. Of course you need to run it with the sheet active that has the correct value of cell E4. After you run it, review VBE's Immediate Window for the results of Debug.Print.

You may need to change the Value2 to Text if E4 is formatted date. I put the result of FileFolder into the Immediate window with Debug.Print so you can check if the FileFolder string was correct.

Sub Test_SearchFiles() 
    Dim v As Variant, a() As Variant, FileFolder As String
    FileFolder = "\\sptd.sabert.net\sites\op\olt\Production Supervisors\HouseKeeping\Completed_Checklist\" & Range("E4").Value2 & "\" 
     Debug.Print "FileFolder = ", FileFolder
     'FileFolder = ThisWorkbook.Path
    SearchFiles FileFolder, "*.*", 0, a(), False
     'Debug.Print UBound(a(), 1), UBound(a(), 2)
    For Each v In a() 
        Debug.Print v 
    Next v 
End Sub 
 
 ' Returns 2 dimension array.  e.g. a(1,1)=Path, a(2,1)=Filename
Function SearchFiles(myDir As String _ 
    , myFileName As String, n As Long, myList() _ 
    , Optional SearchSub As Boolean = False) As Variant 
    Dim fso As Object, myFolder As Object, myFile As Object 
    Set fso = CreateObject("Scripting.FileSystemObject") 
    For Each myFile In fso.getfolder(myDir).Files 
        Select Case myFile.Attributes 
        Case 2, 4, 6, 34 
        Case Else 
            If (Not myFile.Name Like "~$*") _ 
            * (myFile.Path & "\" & myFile.Name <> ThisWorkbook.FullName) _ 
            * (UCase(myFile.Name) Like UCase(myFileName)) Then 
                n = n + 1 
                Redim Preserve myList(1 To 2, 1 To n) 
                myList(1, n) = myDir 
                myList(2, n) = myFile.Name 
            End If 
        End Select 
    Next 
    If SearchSub Then 
        For Each myFolder In fso.getfolder(myDir).subfolders 
            SearchFiles = SearchFiles(myFolder.Path, myFileName, _ 
            n, myList, SearchSub) 
        Next 
    End If 
    SearchFiles = IIf(n > 0, myList, CVErr(xlErrRef)) 
End Function