尝试使用关键字(VBA-Access)在文件夹中寻找文件 [英] Recusively trying to find file within folder using keyword (VBA-Access)

问题描述

我正在创建一个带有下拉框Combo_History的vba访问应用程序，该应用程序使用户能够从主文件夹扫描的工作订单(存档)"中的子文件夹启动.pdf文件.我想做的是使用一个称为"M"的号码(M的号码，因为每个数字均以M开头:M765196)查找此文件，而无需使用特定的子文件夹，这是我到目前为止所拥有的:

I am creating an vba-access application with a drop down box Combo_History that gives the user the ability to launch a .pdf file from a sub-folder within a main folder called "Scanned Work Orders (Archives)". What I am trying to do is use a certain number called an "M" number(M number because every number starts with an M ex: M765196) to find this file without using a specific sub folder here is what i have so far:

Dim fso，oFolder，oSubfolder，oFile，作为集合排队

Dim fso, oFolder, oSubfolder, oFile, queue As Collection

Set fso = CreateObject("Scripting.FileSystemObject")
Set queue = New Collection
queue.Add fso.GetFolder("T:\Scanned Work Orders (Archives)") 

Do While queue.Count > 0
    Set oFolder = queue(1)
    queue.Remove 1 'dequeue
    If oFile = Combo_History.Value Then
            Application.FollowHyperlink ("T:\Scanned Work Orders (Archives)" & oFile)

        End If
    For Each oSubfolder In oFolder.SubFolders
        queue.Add oSubfolder 'enqueue
    Next oSubfolder
    For Each oFile In oFolder.Files
        If oFile = Combo_History.Value Then
            Application.FollowHyperlink ("T:\Scanned Work Orders (Archives)" & oFile)

        End If
    Next oFile
Loop

问题在于它陷入了无限循环，因为即使在该文件夹中也找不到关键字名称为M765196的.pdf.我有什么想念的吗?还是找到.pdf文件的简便方法?

The problem is it gets stuck in an infinite loop because it cannot find the .pdf with the keyword name M765196 even though it is in that folder. Is there something im missing? Or an easier way to find the .pdf file?

推荐答案

我在这里添加第二个答案，因为求解通配符的差异与原先的预期相比有所不同.

I'm adding a second answer here because solving for a wildcard differed more than I anticipated from the original.

使用通配符搜索文件并不难，但是会带来一些影响，例如返回结果列表而不是单个结果.另外，幸运的是，我在一个子文件夹中遇到了权限错误，这使我开始思考如何处理这种情况.

Searching for files using a wildcard isn't difficult, but it comes with some implications, such as returning a list of results instead of a single result. In addition, I fortunately ran into a permissions error on one of my subfolders which caused me to think about how to handle that situation.

Option Explicit

Private recurseDepth As Integer

Sub test()
    Dim rootFolder As String
    Dim filename As String
    Dim resultFiles() As String
    Dim i As Integer

    rootFolder = "C:\Temp"
    filename = "*.pdf"

    If FindFiles(rootFolder, filename, resultFiles) > 0 Then
        For i = 1 To UBound(resultFiles)
            Debug.Print Format(i, "00") & ": " & resultFiles(i)
        Next i
    Else
        Debug.Print "No files found!"
    End If
End Sub

Public Function FindFiles(thisFolder As String, filespec As String, _
                          ByRef fileList() As String) As Integer
    '--- starts in the given folder and checks all files against the filespec.
    '    the filespec MAY HAVE A WILDCARD specified, so the function returns
    '    an array of full pathnames (strings) to each file that matches
    '      Parameters:  thisFolder - string containing a full path to the root
    '                                folder for the search
    '                   filespec   - string containing a single filename to
    '                                search for, --or--
    '                                string containing a wildcard string of
    '                                files to search for
    '        (result==>)fileList   - an array of strings, each will be a full
    '                                path to a file matching the input filespec
    '         Returns:  (integer) count of the files found that match the filespec
    On Error GoTo Error_FindFile
    Static fso As Object
    Static pathCollection As Collection
    Dim fullFilePath As String
    Dim oFile As Object
    Dim oFolder As Object
    Dim oSubfolder As Object

    '--- first time through, set up the working objects
    If recurseDepth = 0 Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set pathCollection = New Collection
    End If
    recurseDepth = recurseDepth + 1

    '--- focus on the given folder
    Set oFolder = fso.GetFolder(thisFolder)

    '--- first test if we have permissions to access the folder and
    '    if there are any files in the folder
    On Error Resume Next
    If oFolder.Files.Count > 0 Then
        If Err.Number = 0 Then
            '--- loop through all items in the folder. some are files and
            '    some are folders -- use recursion to search the subfolders
            For Each oFile In oFolder.Files
                If oFile.Name Like filespec Then
                    pathCollection.Add oFolder.Path & "\" & oFile.Name
                End If
            Next oFile
            For Each oSubfolder In oFolder.SubFolders
                FindFiles oSubfolder.Path, filespec, fileList
            Next oSubfolder
        Else
            '--- if we get here it's usually a permissions error, so
            '    just skip this folder
            Err.Clear
        End If
    End If
    On Error GoTo Error_FindFile

Exit_FindFile:
    recurseDepth = recurseDepth - 1
    If (recurseDepth = 0) And (pathCollection.Count > 0) Then
        '--- pull the paths out of the collection and make an array, because most
        '    programs uses arrays more easily
        ReDim fileList(1 To pathCollection.Count)
        Dim i As Integer
        For i = 1 To pathCollection.Count
            fileList(i) = pathCollection.Item(i)
        Next i
    End If
    FindFiles = pathCollection.Count
    Exit Function

Error_FindFile:
    Debug.Print "Error (" & Err.Number & "): " & Err.Description & _
                        " on " & oSubfolder.Path
    GoTo Exit_FindFile

End Function

这篇关于尝试使用关键字(VBA-Access)在文件夹中寻找文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！