Search for a folder (1 Viewer)

[RCurtin]

Hi,
I have a folder for each of 80 employees. The folder name is made up of the employees name and employee number e.g. Aldea, Robert 34

In each folder are timesheet files for that employee. Basically I need the code to find the correct employee folder and list the files in it. I have seen lots of posts about searching for files but can't find out how to search for a folder. Here is my code:

 strTestFolder = "C:\Monthly Employees\Aldea, Robert 34\" [COLOR="green"]'this works[/COLOR]
    
    strBaseFolder = "C:\Monthly Employees\"
    empNum = 34
    [COLOR="Red"]empFolder = strBaseFolder & "*" & empNum & "\"[/COLOR]
   
    Debug.Print "test: " & strTestFolder
    Debug.Print "empl: " & empFolder
    
    With Application.FileSearch
        .NewSearch
       [COLOR="Green"]' .LookIn = strTestFolder  'this works - prints out the files in the folder[/COLOR]        
        .LookIn = empFolder [COLOR="Green"]'this doesn't work[/COLOR]
        .Filename = "*.*"
    
        If .Execute > 0 Then        [COLOR="Green"]'if the file searched for exists[/COLOR]            
            folderTimesheets = .FoundFiles(1)
            Debug.Print folderTimesheets
        End If
    End With

Here is what I get in the immediate window:

test: C:\Monthly Employees\Aldea, Robert 34\
empl: C:\Monthly Employees\*34\

I don't know if I should be doing a special search for a folder of if its just a problem with string comparisons. Any ideas?

 

[ejstefl]

You can use the Dir() command to check if a folder exists. Check out VBA help.

 

[Kenln]

This is from the Microsoft Knowledge Base.
The first examples look for INI and TXT files which, I know you didn't ask for but it came with the example.

The next example uses the variable MyName to display a list of directory names. You should be able to If Then this or something along those lines to search for a directory name.


Dim MyFile, MyPath, MyName
' Returns "WIN.INI" (on Microsoft Windows) if it exists.
MyFile = Dir("C:\WINDOWS\WIN.INI")

' Returns filename with specified extension. If more than one *.ini
' file exists, the first file found is returned.
MyFile = Dir("C:\WINDOWS\*.INI")

' Call Dir again without arguments to return the next *.INI file in the
' same directory.
MyFile = Dir

' Return first *.TXT file with a set hidden attribute.
MyFile = Dir("*.TXT", vbHidden)

' Display the names in C:\ that represent directories.
MyPath = "c:\" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is a directory.
If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
Debug.Print MyName ' Display entry only if it
End If ' it represents a directory.
End If
MyName = Dir ' Get next entry.
Loop

 

[RCurtin]

Many thanks for the help - I got that working!

myPath = "C:\Monthly Employees\"
    myName = Dir(myPath, vbDirectory) ' Retrieve the first entry.
    
    Do While myName <> "" ' Start the loop.
        ' Ignore the current directory and the encompassing directory.
        If myName <> "." And myName <> ".." Then
            ' Use bitwise comparison to make sure MyName is a directory.
            If (GetAttr(myPath & myName) And vbDirectory) = vbDirectory Then
                If InStr(myName, empNum) > 0 Then
                    Debug.Print myName ' Display entry only if it
                Else
                    Debug.Print "not found"
                End If
            End If ' it represents a directory.
        End If
        myName = Dir ' Get next entry.
    Loop