none
Microsoft Access 2010 FindFirstFile API RRS feed

  • Question

  • I was wondering if it is possible to search through Folders and sub-directories to find a file using a windows API FindFirstFile and a wild card for a partial name. If so how would I go about doing this instead of using the recursive method?
    • Edited by Oneill M Wednesday, July 29, 2015 1:06 PM
    Wednesday, July 29, 2015 1:06 PM

Answers

  • Good. Then you don't need recursion.

    Note that in the code below we make one pass to get the folders, then one pass to get the files in each folder.

        Dim rootFolder  As String
        Dim subFolder   As String
        Dim fileSpec    As String
        Dim filename    As String
        Dim subfolders() As String
        Dim intSubFolderCount As Integer

        rootFolder = "E:\M2Dev\MVP\OneilMclean\Scanned Files\"
        subFolder = Dir(rootFolder & "*.*", vbDirectory)
       
        '*** Get subfolders in array ***
        While subFolder <> ""
            If subFolder <> "." And subFolder <> ".." Then
                ReDim Preserve subfolders(intSubFolderCount)
                subfolders(intSubFolderCount) = subFolder
                intSubFolderCount = intSubFolderCount + 1

                Debug.Print subFolder
            End If
            subFolder = Dir()
        Wend

        '*** Loop over array and find files ***
        fileSpec = Me.Combo_History & "*.pdf    'Note use of * rather than ?"   
        For intSubFolderCount = 0 To UBound(subfolders)
            filename = Dir(rootFolder & subfolders(intSubFolderCount) & "\" & fileSpec)
            While filename <> ""
                Debug.Print rootFolder & subfolders(intSubFolderCount) & "\" & filename
                filename = Dir()    'Get the next file.
            Wend

        Next intSubFolderCount


    -Tom. Microsoft Access MVP


    Monday, August 3, 2015 1:39 PM

All replies

  • Hi Oneil Mclean,

    Thanks for posting in MSDN forum. This forum is for developers discussing developing issues about Access.

    >>I was wondering if it is possible to search through Folders and sub-directories to find a file using a windows API FindFirstFile and a wild card for a partial name.<<

    Yes. Based on the description of this API, it is possible.

    Searches a directory for a file or subdirectory with a name that matches a specific name (or partial     name if wildcards are used).

    >> If so how would I go about doing this instead of using the recursive method?<<

    This function support searching the files in subdirectory, we are not necessary to write recursive method. If you still have problem about Windows API, I suggest that you reopen a new thread in General Windows Desktop Development Issues  forum.

    If you have any problem with Access developing, please feel free to let us know.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 30, 2015 2:40 AM
    Moderator
  • @Fei: Although the OP is not very clear about this, I think he wants to find a file in an arbitrarily deep directory tree. As such a single call to FindFirstFile would not be sufficient.

    @Oneil: Why not use the recursive method like everyone else? Be careful about reparse points.
    Also, VBA's Dir function can do the job. No need to drag in another library.


    -Tom. Microsoft Access MVP

    Thursday, July 30, 2015 3:25 AM
  • Tom,

    The reason why I don't want to use a recursive method is do to the amount of files in this particular location is too large to recursively search, and it causes access to freeze up when I run it.

    - Oneill

    • Edited by Oneill M Thursday, July 30, 2015 12:41 PM
    Thursday, July 30, 2015 12:38 PM
  • That is normal for any long-running operation that does not yield the CPU. A well-placed DoEvents can take care of that.

    Note that this comes with its own perils as it makes the code re-entrant. Which you can guard against with careful programming.


    -Tom. Microsoft Access MVP

    Thursday, July 30, 2015 12:50 PM
  • Tom,

    This is my code so far, what im looking for is a file name that is like this "M241970 9-25-98 AMI.pdf" in a subfolder AMI within a folder named "Scanned Files" What i am using to search is the "M241970" which is in a combo box named Combo_History  this is the code i am using. I'm not sure what to do to get that file with just the "M241970":

    Private Sub Command132_Click()
    On Error GoTo Err_Command132_Click
    
        
        Call test
    
    Exit_Command132_Click:
        Exit Sub
    
    Err_Command132_Click:
        MsgBox Err.Description
        Resume Exit_Command132_Click
        
    End Sub
    
    
    
    
    ' Sets the variables for the search 
    Sub test()
        Dim rootFolder As String
        Dim filename As String
        Dim resultFiles() As String
        Dim i As Integer
        
        rootFolder = ScannedWOdir
        filename = Me.Combo_History & "?.pdf"
        
        ' give the full path and open pdf when found
        If FindFiles(rootFolder, filename, resultFiles) > 0 Then
            For i = 1 To UBound(resultFiles)
                Debug.Print Format(i, "00") & ": " & resultFiles(i)
                Application.FollowHyperlink resultFiles(i)
            Next i
        Else
            MsgBox "PDF Not Found.", vbOKOnly
        End If
    End Sub
    
    
    
    
    
    ' Does the searching
    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

    Thursday, July 30, 2015 1:58 PM
  • You wrote:what im looking for is a file name that is like this "M241970 9-25-98 AMI.pdf" in a subfolder AMI within a folder named "Scanned Files" What i am using to search is the "M241970" which is in a combo box named Combo_History  this is the code i am using. I'm not sure what to do to get that file with just the "M241970":

    My questions:
    1. Are you always looking in the subfolder AMI (off of some RootFolder) within Scanned Files, or is there a larger and perhaps unknown deep folder tree to search?

    Assuming the simple RootFolder\Scanned Files\AMI\ComboHistory*.pdf, the answer is:

        Dim rootFolder As String
        Dim subFolder As String
        Dim fileSpec As String
        Dim filename As String
       
        rootFolder = "E:\M2Dev\MVP\OneilMclean\"
        subFolder = "Scanned Files\AMI\"
        fileSpec = Me.Combo_History & "*.pdf    'Note use of * rather than ?"
        filename = Dir(rootFolder & subFolder & fileSpec)
        While filename <> ""
            filename = Dir() 'Get the next file.
            Debug.Print filename
        Wend


    -Tom. Microsoft Access MVP

    Friday, July 31, 2015 2:19 AM
  • Tom,

    This is similar to what I have had written before, but in my case i would not know exactly which sub folder the file is in being that I have about 120 folders within "Scanned Files" folder. Just to clarify with the application i am modifying the only way to find the file is by using what ever is in the combo box Combo_History, also the root path would be as follows: "T:\Scanned Files\" and from there they're multiple sub folders (AMI, GHB etc.). That's why I used the function to recursively run through all sub folders and all files within those sub folders. Long story short if i wanted to use this code I would have to type subFolder = "".

    P.S. Scott Says hello
    • Edited by Oneill M Friday, July 31, 2015 2:29 PM
    Friday, July 31, 2015 2:27 PM
  • Not sure who Scott is - enlighten me.

    So you have a folder tree:
    Root: T:\Scanned Files\
      [120 subfolders such as AMI, GHB]
    Then you have files in these subfolders, and you are trying to find whatever is in Me.Combo_History*.pdf

    You have no sub-subfolders.

    Correct so far?


    -Tom. Microsoft Access MVP

    Saturday, August 1, 2015 4:23 AM
  • Correct, there are no more sub folders other than the  [120 subfolders such as AMI, GHB] withing the root folder.

    P.S Scott Gem Microsoft Access MVP



    • Edited by Oneill M Monday, August 3, 2015 1:28 PM
    Monday, August 3, 2015 12:48 PM
  • Good. Then you don't need recursion.

    Note that in the code below we make one pass to get the folders, then one pass to get the files in each folder.

        Dim rootFolder  As String
        Dim subFolder   As String
        Dim fileSpec    As String
        Dim filename    As String
        Dim subfolders() As String
        Dim intSubFolderCount As Integer

        rootFolder = "E:\M2Dev\MVP\OneilMclean\Scanned Files\"
        subFolder = Dir(rootFolder & "*.*", vbDirectory)
       
        '*** Get subfolders in array ***
        While subFolder <> ""
            If subFolder <> "." And subFolder <> ".." Then
                ReDim Preserve subfolders(intSubFolderCount)
                subfolders(intSubFolderCount) = subFolder
                intSubFolderCount = intSubFolderCount + 1

                Debug.Print subFolder
            End If
            subFolder = Dir()
        Wend

        '*** Loop over array and find files ***
        fileSpec = Me.Combo_History & "*.pdf    'Note use of * rather than ?"   
        For intSubFolderCount = 0 To UBound(subfolders)
            filename = Dir(rootFolder & subfolders(intSubFolderCount) & "\" & fileSpec)
            While filename <> ""
                Debug.Print rootFolder & subfolders(intSubFolderCount) & "\" & filename
                filename = Dir()    'Get the next file.
            Wend

        Next intSubFolderCount


    -Tom. Microsoft Access MVP


    Monday, August 3, 2015 1:39 PM
  • This is perfect a lot faster than the other way i was doing it, but it seems I'm having the same problem as before. It runs through every folder and every file but it still is not opening/finding the file. I've even went into that folder just to make sure the file is there and it is. But for some reason it gives me an error message "Bad file name or Number".
    Monday, August 3, 2015 2:00 PM
  • It works fine for me. You will have to use the debugger and step through carefully to find out what's going on. Maybe the combobox is not returning what you think it is. To test this, temporarily replace with:
            'fileSpec = Me.Combo_History & "*.pdf    'Note use of * rather than ?"
            fileSpec = "M241970*.pdf"

    Note that my code is not opening any file, just enumerating them.

    Was the file you're looking for in the debug.print output?


    -Tom. Microsoft Access MVP

    Monday, August 3, 2015 2:07 PM
  • Hello Tom,

    It seems one piece was missing from the puzzle i forgot to enter the asterisk. So long story short it does work just my silly mistake. Thank You so much for your help.

    -Oneill

    • Edited by Oneill M Monday, August 3, 2015 5:49 PM Found answer
    Monday, August 3, 2015 2:28 PM