VBA Excel find a file (partial file name) in a set of folders (doesn't return a value and doesn't exit function)

M

madmiddle

New Member
Joined
Mar 8, 2012
Messages
45
Good Morning/Afternoon/Evening,

I'm normally ok with VBA in Excel but i'm really struggling to understand what I have done wrong here. My end goal is to search for a file (using only the first part of the file name) in a set of folders and then send back to the calling function the full file name and then containing folder.

The code runs and finds the file but instead of Exiting the ListMyFiles function it simply moves onto the next folder looking for the same file until it has searched all the folders and then moves to the next file. At no point does it return to the calling function and report the file and folder.

have I missed something with the Exit Function ? do i need to add a Booleon instead ?

any help is much appreciated

TYIA

Andy

Code: 
[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] iRow[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Counter
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] myFile [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Scripting[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]File
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ListFiles[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] WS1[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] WS2 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheet
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] strDesignDocs [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]On[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Error[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Resume[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] WS1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Data"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] WS2 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Clean_Up"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] WS1
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] strDesignDocs [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit](.[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Cells[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]15[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Cells[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]27[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]))[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cell [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] strDesignDocs
        strReturnValue [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ListMyFiles[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]ComboBox21[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        Debug[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Print strReturnValue
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ListMyFiles[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]mySourcePath[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] IncludeSubfolders[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sFileName[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyObject [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Scripting[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FileSystemObject

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]On[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Error[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Resume[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyObject [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#101094][FONT=inherit]New[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Scripting[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FileSystemObject
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] mySource [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyObject[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]GetFolder[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]mySourcePath[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] myFile [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] mySource[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Files
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] InStr[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] myFile[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sFileName[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<>[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            strReport [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] myFile[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]" in "[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] myFile[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Path
            ListMyFiles [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] strReport
            [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'Debug.Print strReport[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Exit[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] IncludeSubfolders [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] mySubFolder [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] mySource[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]SubFolders
            [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Call[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ListMyFiles[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]mySubFolder[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Path[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sFileName[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
I think your problem might be because you have called the function recursively. So you exit the lowest level of the subfolders but then carry on at all the levels higher up. I suggest set a "found" flag to false at the start and then set it true when you have found the file, and then test for true at every loop before the loop. this should get you out of all levels.
 
Upvote 0
offthelip said:
I think your problem might be because you have called the function recursively. So you exit the lowest level of the subfolders but then carry on at all the levels higher up. I suggest set a "found" flag to false at the start and then set it true when you have found the file, and then test for true at every loop before the loop. this should get you out of all levels.
Click to expand...

Thank you for the reply, I can see how that should work but I've tried several versions including:

Code: 
Dim iRowDim Counter
Dim myFile As Scripting.File
Sub ListFiles()
    Dim WS1, WS2 As Worksheet
    Dim strDesignDocs As Variant
    On Error Resume Next


    Set WS1 = Sheets("Data")
    Set WS2 = Sheets("Clean_Up")


    With WS1
        Set strDesignDocs = .Range(.Cells(15, 1), .Cells(27, 1))
    End With


    For Each cell In strDesignDocs
        strReturnValue = ListMyFiles(Sheets("Clean_Up").ComboboxFrom.Value, True, cell.Value, False)
        Debug.Print strReturnValue
    Next
End Sub


Function ListMyFiles(mySourcePath, IncludeSubfolders, sFileName, blnFound)
    Dim MyObject As Scripting.FileSystemObject


    On Error Resume Next
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)


    If blnFound = False Then


        For Each myFile In mySource.Files
            If InStr(1, myFile.Name, sFileName) <> 0 Then
                strReport = myFile.Name & " in " & myFile.Path
                ListMyFiles = strReport
                'Debug.Print strReport
                blnFound = True
                Exit For
            End If
        Next
    
        If IncludeSubfolders Then
            For Each mySubFolder In mySource.SubFolders
                Call ListMyFiles(mySubFolder.Path, True, sFileName, False)
            Next
        End If
    ElseIf blnFound = True Then
        Exit Function
    
    End If
    
End Function

which doesn't seem to exit all the way out for some reason it just moves to the next folder and carries on and also doesn't return the message back to the calling function.
 
Upvote 0
You haven't put the blnfound test in the right place
you need to add this line straight after the for each myfile in source.files:
Code: 
 For Each myFile In mySource.Files      
' add this line
  If blnFound Then Exit For
 
Last edited:
Upvote 0
offthelip said:
You haven't put the blnfound test in the right place
you need to add this line straight after the for each myfile in source.files:
Code: 
 For Each myFile In mySource.Files      
' add this line
  If blnFound Then Exit For
Click to expand...

Like this ?:

Code: 
Dim iRow
Dim Counter
Dim myFile As Scripting.File
Sub ListFiles99()
    Dim WS1, WS2 As Worksheet
    Dim strDesignDocs As Variant
    On Error Resume Next
    Set WS1 = Sheets("Data")
    Set WS2 = Sheets("Clean_Up")
    With WS1
        Set strDesignDocs = .Range(.Cells(15, 1), .Cells(27, 1))
    End With
    For Each cell In strDesignDocs
        strReturnValue = ListMyFiles99(Sheets("Clean_Up").ComboboxFrom.Value, True, cell.Value, False)
        Debug.Print strReturnValue
    Next
End Sub
Function ListMyFiles99(mySourcePath, IncludeSubfolders, sFileName, blnFound)
    Dim MyObject As Scripting.FileSystemObject
    On Error Resume Next
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
        For Each myFile In mySource.Files
            If blnFound Then Exit For
            If InStr(1, myFile.Name, sFileName) <> 0 Then
                strReport = myFile.Name & " in " & myFile.Path
                ListMyFiles = strReport
                'Debug.Print strReport
                blnFound = True
                'Exit For
            End If
        Next
    
        If IncludeSubfolders Then
            For Each mySubFolder In mySource.SubFolders
                Call ListMyFiles99(mySubFolder.Path, True, sFileName, False)
            Next
        End If
    
End Function
 
Upvote 0
Not quite , you haven't initialised the blnfound flag to false, so it is undefined when you first test it, this does create a bit of a problem in how to initialise it i will have think about it.
On second thought I think that should work
 
Last edited:
Upvote 0
I need help with vba excel to look into the shared drive (folders and subfolders) for either a partial or exact match on a cell and returns the file hyperlink/s. It's like a search engine but limited to excel files. Please.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Hyperlink on VBA
Replies
2
Views
61
MSqueries
M
D
  • Question Question
Check if sheet exists while looping through workbooks
Replies
8
Views
4K
dmt32
D
L
  • Question Question
VBA count number of emails in Outlook folder/subfolders, rumtime error 5
Replies
4
Views
2K
lyric025
L
E
  • Question Question
How to import CSV data into multiple arrays and to return the multiple arrays via a function or sub in VBA?
Replies
0
Views
2K
excelvbalearner
E
N
  • Question Question
Count the total lines in all files in all subfolders
Replies
8
Views
1K
Zak Naz
Z

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top