excel中，根据文件名查询文件路径

excel内置函数，没有提供文件搜索的功能。所以引入VBA代码，来进行辅助。

Sub CheckFilesExistence()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim fileName As String
    Dim fso As Object
    Dim basePath As String
    Dim result As String
    
    ' 设置工作表和基础路径（最好使用绝对路径）
    Set ws = ThisWorkbook.Sheets(1)
        basePath = "myfolder" ' 修改为实际路径
    
    ' 初始化文件系统对象。使用
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 获取A列最后一行
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 预处理：缓存所有文件路径（以文件名为键，路径为值）
    Dim allFiles As Object
    Set allFiles = GetFilesInFolderTree(fso, basePath)
    
    ' 遍历A列文件名，快速查询缓存并返回路径
    For i = 1 To lastRow
        fileName = ws.Cells(i, 1).Value
        If fileName <> "" Then
            ' 直接检查文件名是否存在于缓存
            If allFiles.Exists(fileName) Then
                result = "存在，路径：" & allFiles(fileName)
            Else
                result = "不存在"
            End If
            ws.Cells(i, 2).Value = result
        Else
            ws.Cells(i, 2).Value = "文件名为空"
        End If
    Next i
    
    Set allFiles = Nothing
    Set fso = Nothing
End Sub

' 预加载目录树内所有文件路径到字典，通过队列，避免递归
Function GetFilesInFolderTree(fso As Object, ByVal folderPath As String) As Object
    Dim filesDict As Object
    Set filesDict = CreateObject("Scripting.Dictionary")
    
    ' 检查路径是否有效
    If Not fso.FolderExists(folderPath) Then Exit Function
    
    Dim queue As Object
    Set queue = CreateObject("System.Collections.Queue") ' 使用队列实现迭代
    queue.Enqueue fso.GetFolder(folderPath)
    
    Dim currentFolder As Object
    Dim subFolder As Object
    Dim file As Object
    
    ' 非递归遍历所有文件夹
    Do While Not queue.Count = 0
        Set currentFolder = queue.Dequeue()
        
        ' 遍历当前文件夹下的所有文件，存入字典（文件名为键，完整路径为值）
        For Each file In currentFolder.Files
            ' 存储格式：Key=文件名，Value=完整路径（含文件名），按格式需要调整
            filesDict(file.Name) = file.Path
        Next
        
        ' 将子文件夹加入队列继续处理
        For Each subFolder In currentFolder.SubFolders
            queue.Enqueue subFolder
        Next
    Loop
    
    Set GetFilesInFolderTree = filesDict
End Function

filesystemobject参考