Searching Directory structure for specific directory

[togo]

We have a database with folders A-Z for client names, inside these folders are job numbers for each client



been searching for code designed to work within Access, we know what job number we want and probably the first letter of the client name, but need to be able to search the whole directory for the job sub folder since some names may not be correct

any suggestions, thanks.

[rpeare]

Use the filesystemobject method. Here's an example I did that's looking for .mdb files (also does an import function which you can ignore). You can also modify the code to allow user input search criteria.

MasterDB.zip

[togo]

k is it possible to do it, searching for subdirectories without files in them?

[rpeare]

if you just want to list subdirectories yes, it's the same method, just adapt it to your situation.

[togo]

but according to this

fso.BuildPath(fld.Path, sFile)

it requires a file to search for in order to return directory information?

[rpeare]

yes, so what's the problem?

You can modify the buildpath function to just look for folders and ignore the file component altogether, that's what I meant about adapting it to your situation. When I was building that example nothing had the entirety of what I needed, I adapted code I found elsewhere to what I wanted. I'm giving you the code you just have to stop the parts from working you don't want.

[togo]

this is what I have now

Private Sub Command68_Click()
sPath = "T:\CQI CLIENTS\"
spathMember = Dir(sPath, vbDirectory) ' Get the first entry
bracketFound = False
a = 2
DirectorySearch:
Do While spathMember <> ""   ' Start the loop
  ' Be sure to skip over these directory markers
  If MyName <> "." And MyName <> ".." Then
    If Right(spathMember, 1) <> "." And Right(spathMember, 2) <> ".." Then
        'Test if directory
        If (GetAttr(sPath & spathMember) And vbDirectory) = vbDirectory Then
            'it is a directory, try to see if the directory name contains the job number.
            'Job number example: I121000100 (9 numerical digits)
            If Len(spathMember) >= 9 Then
                For i = 1 To Len(spathMember) - 8
                    If Mid(spathMember, i, 9) = Me.JOBNUMBER Then JobNumberHasBeenFound = True
                Next
                If spathMember = Me.JOBNUMBER Then JobNumberHasBeenFound = True
            End If
                    
            If JobNumberHasBeenFound = True Then
                StorePDF = sPath & spathMember & "\"
                Me.Requery
                Me.Refresh
                GoTo ExitFolderSearch
                Else
                'otherwise, sPathMember gets added to sPath
                sPath = sPath & spathMember & "\"
                spathMember = Dir(sPath, vbDirectory)
                GoTo DirectorySearch
            End If
            
            If JobNumberHasBeenFound = False Then GoTo TryNextFolder
        End If
    End If
  End If
TryNextFolder:
  spathMember = Dir   ' Next entry
  If spathMember = "" Then ' that was the last folder in this directory, need to go up one directory with the sPath string.
        ' so long as the level stays at CQI clients, otherwise exit loop we haven't found the job
        ' search for the first backslash we can find
        Do While bracketFound = False
            If Mid(sPath, Len(sPath) - a, 1) = "\" Then bracketFound = True
            a = a + 1
        Loop
        If Right(sPath, 12) = "CQI Clients\" Then MsgBox "Unable to find job directory.": GoTo ExitFolderSearch
        sPath = Left(sPath, Len(sPath) - a)
        ' if we reached this point then we're up a directory and it isn't CQI CLIENTS and the search needs to continue.
        ' goto directorysearchagain.
        GoTo DirectorySearch
    End If
Loop
ExitFolderSearch:
End Sub