Check if a file exists in subfolders by looping through and matching an excel range partial

[Duhwellhuh]

Hello everyone

This is my first post. I've spent all day trying to figure this one out. What I'm trying to do is check the existence of files in subdirectories by matching text in an excel range ("A:A"). The text within each cell in the excel range are partial filenames. So far, I've been using filesystem object and wild cards and a like statement within an if statement, but it doesn't work. Some files appear to be available, while others aren't. Any help would be truly appreciated, here's what I got:

Sub GetSubFiles()
Dim fso, fldr, subFldr, oFiles, oFile, myBaseName
Dim mySF, myName, strName As String
Dim myRange As Range



mySF = "G:\"


    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder(mySF)
    
    Set myRange = Sheets(2).Range("A:A")
    
    
    For Each fn In myRange
    For Each subFldr In fldr.SubFolders
    Set oFiles = subFldr.Files

        For Each oFile In oFiles

        myName = fso.GetBaseName(oFile)
      
        
        If fn.Value = "" Then
        Exit Sub
        Else
        'Debug.Print fn.Value
        
        
      If LCase("*" & fn.Value & "*") Like (LCase("*" & myName & "*")) Then
                fn.Offset(0, 1).Value = "Available"
                fn.Offset(0, 1).Interior.Color = RGB(0, 255, 0)
                 
                 Else
                fn.Offset(0, 1).Value = "Not Available"
                fn.Offset(0, 1).Interior.Color = RGB(255, 0, 0)


           End If
           End If
Next
Debug.Print myName
Next
Next
             
     

    Set oFiles = Nothing
    Set subFldr = Nothing
    Set fldr = Nothing
    Set fso = Nothing
End Sub

 

[wigi]

HThe text within each cell in the excel range are partial filenames.

Can you give examples please?

About how many files do have in G: ?

 

[Duhwellhuh]

The amount of files will vary. An example would be this:

In column A, I will have a number of partial filenames (count can vary here as well). For each of the cells in column A, I want to search an entire directory called G:\ for files that match the partial filename in column A.

Let's say in Column A the first cell reads 00-TEMP. I need the code to search the directory for a file whose name is similar to 00-TEMP. It could be 00-TEMPLATE or 00-TEM. If the file matches the partial filename in any of the mentioned in Column A and is confirmed to or not to exist, I want to write either "Available" or "Not Available" in the next column to the right. After that it would go to the next cell in the range, do the process, and stop when the cells are empty.

I've tried multiple placement of the FOR statements with no avail. It keeps stopping at the upper most folder. I hope you have a solution because I'm at wit's end.

Thanks for responding!

 

[wigi]

What is time consuming, is setting up the list of files.
You should loop once through all files, and store them in a worksheet or an array.
After that, a simple search function could be written in column B, to get "Available" or "Not Available". Conditional formatting could do the coloring.
Reduce the usage of VBA and certainly, loops of these kinds.

 

[Duhwellhuh]

Thanks, I appreciate your time and response. I'll give it a try and see what I come up with.

 

[Duhwellhuh]

Do you have any pointers or code snippets to do what you suggest with looping through all files and storing them and then searching?

 

[wigi]

For example:

http://www.mrexcel.com/forum/showthread.php?t=464766

 

[Duhwellhuh]

Thanks for the link Wigi! With that info, how would I do what I'm trying to do with subfolders? I have since found something that helped, but it is very slow and freezes up when searching the folders. I agree with you about the arrays, it does make more since to use them rather than looping several times. Tell me if you can see any way I could make this faster. I see that it is using arrays, but I'm unsure if it is the most efficient way of doing it.

This is what I came across:

Dim myRange As Range
Sub FileExist()
    Dim myDir As String, temp(), myList, myName As String
    Dim SearchSubFolders As Boolean, Rtn As Integer, msg As String
 
    Set myRange = Sheets(3).Range("A:A")
 
    For Each cell In myRange
    If cell = "" Then
    Exit Sub
    Else
    myDir = "G:\"
    myName = "*" & cell.Value & "*"
 
 
    myList = SearchFiles(myDir, myName, 0, temp(), True)
    If Not IsError(myList) Then
                cell.Offset(0, 1).Value = "Available"
                cell.Offset(0, 1).Interior.Color = RGB(0, 255, 0)
                cell.Offset(0, 2).Value = myFileName
        Application.Transpose (myList)
    Else
                cell.Offset(0, 1).Value = "Not Available"
                cell.Offset(0, 1).Interior.Color = RGB(255, 0, 0)
 
    End If
    End If
    Next
End Sub
 
 
Private Function SearchFiles(myDir As String _
    , myFileName As String, n As Long, myList() _
    , Optional SearchSub As Boolean = True) 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

thanks

 

[wigi]

Your SearchFiles is inside the loop through the cells.
For every single file in column A, you go out and loop through all files in G:\
Isn't that a little bit illogical?

Why not looping once to get the files, and THEN loop through the entries in column A to see if they match?

Revamp the code to take SearchFiles outside the loop over cells.

 

[Duhwellhuh]

Thanks and you are right wigi. It is illogical to do that. I was doing some testing this morning and realized what it was actually doing. However, I'm not able to figure out how to go about doing what you suggested while still giving the function access to the cell.value. I keep getting an object required error. Any suggestions?