VBA Excel按部分文件名在文件夹中查找文件的代码问题求助

Hey Andy, let's fix up your VBA code step by step—those recursive file search issues are super common, so we'll tackle each problem one by one and get your function working as expected.

First, let's break down the root causes of your issues: #

1. Can't exit the entire function after finding a file: Your original Exit Function only quits the current recursive layer, not the entire nested search. The parent loops keep running through other folders.
2. Can't return results to the calling sub: You weren't capturing the return value from recursive calls, so results from subfolders never made it back to ListFiles.
3. Function keeps processing files after finding a match: Without a way to signal "we found what we need!" across all recursive layers, the search keeps chugging along.

Here's the revised, fixed code with explanations: #

First, update the ListFiles sub to fix variable declarations and handle edge cases:

Sub ListFiles()
    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim strDesignDocs As Range
    Dim cell As Range
    Dim strReturnValue As String
    
    ' Handle missing sheets gracefully
    On Error Resume Next
    Set WS1 = Sheets("Data")
    Set WS2 = Sheets("Clean_Up")
    On Error GoTo 0 ' Reset error handling to avoid masking issues
    
    If WS1 Is Nothing Then
        MsgBox "Sheet 'Data' not found! Exiting."
        Exit Sub
    End If
    
    With WS1
        Set strDesignDocs = .Range(.Cells(15, 1), .Cells(27, 1))
    End With
    
    ' Loop through each target filename, skip empty cells
    For Each cell In strDesignDocs
        If Not IsEmpty(cell.Value) Then
            strReturnValue = ListMyFiles(ComboBox21.Value, True, cell.Value)
            Debug.Print strReturnValue
            ' Optional: Write results to Clean_Up sheet
            ' WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = strReturnValue
        End If
    Next
End Sub

Then rewrite the ListMyFiles function to properly handle recursion and return values:

Function ListMyFiles(mySourcePath As String, IncludeSubfolders As Boolean, sFileName As String) As String
    Dim MyObject As Scripting.FileSystemObject
    Dim mySource As Scripting.Folder
    Dim myFile As Scripting.File
    Dim mySubFolder As Scripting.Folder
    Dim subFolderResult As String
    
    On Error Resume Next
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error GoTo 0 ' Stop ignoring errors after folder setup
    
    ' Check current folder first
    For Each myFile In mySource.Files
        ' Use vbTextCompare to ignore case (remove if you need exact case matching)
        If InStr(1, myFile.Name, sFileName, vbTextCompare) <> 0 Then
            ListMyFiles = myFile.Name & " in " & myFile.Path
            Exit Function ' Exit immediately once we find a match
        End If
    Next
    
    ' If we need to check subfolders and haven't found a match yet
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            subFolderResult = ListMyFiles(mySubFolder.Path, True, sFileName)
            ' If the recursive call found a match, pass it up and exit
            If subFolderResult <> "" Then
                ListMyFiles = subFolderResult
                Exit Function
            End If
        Next
    End If
    
    ' If no match found, return empty string
    ListMyFiles = ""
End Function

Key fixes explained: #

• Proper return value propagation: Instead of using Call for recursive calls, we capture the result in subFolderResult. If a subfolder search finds a file, we immediately pass that result up the recursive chain and exit.
• Full recursion termination: Once a match is found at any level, every layer of the function exits immediately, stopping all further folder traversal.
• Clean variable declarations: Fixed the Dim WS1, WS2 As Worksheet mistake (originally only WS2 was a Worksheet; WS1 was a Variant).
• Edge case handling: Added checks for missing sheets and empty filename cells to avoid errors.
• Optional case insensitivity: Used vbTextCompare in InStr to make searches case-agnostic (remove this parameter if you need exact case matching).

This code will now stop searching as soon as it finds a matching file, return the full path/filename to ListFiles, and print it to the debug window (or write it to your worksheet if you uncomment that line).

内容的提问来源于stack exchange，提问作者Andy M