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

Duhwellhuh

New Member
Joined
Apr 15, 2012
Messages
6
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:

Code:
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
 

Duhwellhuh

New Member
Joined
Apr 15, 2012
Messages
6
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
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

New Member
Joined
Apr 15, 2012
Messages
6
Thanks, I appreciate your time and response. I'll give it a try and see what I come up with.
 

Duhwellhuh

New Member
Joined
Apr 15, 2012
Messages
6
Do you have any pointers or code snippets to do what you suggest with looping through all files and storing them and then searching?
 

Duhwellhuh

New Member
Joined
Apr 15, 2012
Messages
6
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:

Code:
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
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

New Member
Joined
Apr 15, 2012
Messages
6
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?
 

Forum statistics

Threads
1,081,984
Messages
5,362,554
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top