Search for file in a specified location and display needed info 1

[jaabaar]

Writing a function that search any drive specified. In that drive specified it must search all directories and their subsequent subdirectories for a specified excel file and display file name parent drive and location found. For example

1. Search each folder for an excel file
2. If excel file not found in folder and subfolder say so then move to next folder untill all folders are searched
3. If excel found display folder name, location

I have tried to do it but as you can see it does not work and when I managed to do it it never checks for none existence for search sun directories and only does it one.

Public Function SearchForFile()

On Error GoTo SearchForFile_Fail
Dim strDriveLoc As String
Dim strSearchFor As String
Dim strPath As String


Dim strPathMatch As String

strDriveLoc = "G:\" ' Must not say exact location of folder
strSearchFor = "test1.xlsx"
strPath = strDriveLoc & "\" & strSearchFor

strPath = strDriveLoc & "\" & strSearchFor
sFile = Dir(strPath, vbDirectory)

Do While Len(sFile) > 0
'If Left(sFile, 1) <> "." Then
If strDriveLoc <> "." And strDriveLoc <> ".." Then
If (GetAttr(strDriveLoc) And vbDirectory) = vbDirectory Then
strPathMatch = sFile
Debug.Print sFile 'display file name
Debug.Print Dir(strPath, vbDirectory) 'display parent directory
Debug.Print strPath 'display path

'Exit Do
End If
Else
Debug.Print "file not fount in that directory:" & Dir(strPath, vbDirectory)
End If
sFile = Dir
Loop

Exit_SearchForFile:
Exit Function

SearchForFile_Fail:
MsgBox "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
Resume Exit_SearchForFile
End Function


Again thank you in advance for all your help and will be much appreciated.

 

[Andrzejek]

I would start with some 'ready-to-use' examples from here

Have fun.

---- Andy

 

[jaabaar]

Cant beleave it i am lost/stuck unable to move on please can you give me a helping hand

 

[Andrzejek]

From the very first link I gave you (it will give you the whole path to your file and the file name - if found):

Dim colFiles As New Collection
RecursiveDir colFiles, "C:\", "test1.xlsx", True

Dim vFile As Variant
For Each vFile In colFiles
    Debug.Print vFile
Next vFile

'----------------------------

Public Function RecursiveDir(colFiles As Collection, _
                             strFolder As String, _
                             strFileSpec As String, _
                             bIncludeSubfolders As Boolean)

    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop

        'Call RecursiveDir for each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If

End Function

Public Function TrailingSlash(strFolder As String) As String
    If Len(strFolder) > 0 Then
        If Right(strFolder, 1) = "\" Then
            TrailingSlash = strFolder
        Else
            TrailingSlash = strFolder & "\"
        End If
    End If
End Function

Have fun.

---- Andy

 

[jaabaar]

Is it ok to use file name like this as I must use it like this as each file name has a different start name :

RecursiveDir colFiles, "w:", "*ProjectStuff.xlsx", True

as the following shows all folders where the excel are found.

Dim vFile As Variant
For Each vFile In colFiles
Debug.Print vFile
Next vFile

I need to show where each directory entered file not not found: I tried to place it below however it is not working as I need to add another condition to handle not fond, please advice how to do that.

Public Function RecursiveDir(colFiles As Collection, _
                             strFolder As String, _
                             strFileSpec As String, _
                             bIncludeSubfolders As Boolean)
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop
If bIncludeSubfolders Then
  'Fill colFolders with list of subdirectories of strFolder
       strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                Debug.Print "Searching inside folder: " & strTemp
            If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                  colFolders.Add strTemp
                    'where do I add this as it must only show when file is not found in directory
                    [b]Debug.Print "File not found found in directory: " & strTemp[/b]
                End If
            End If
            strTemp = Dir
        Loop
        'Call RecursiveDir function to seach each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If
End Function

I hope that makes sense, again thank you very much for all your advice and help. really appriciate it.

 

[Andrzejek]

add another condition to handle not fond" - is that what you are looking for:

Dim colFiles As New Collection[blue]
Dim blnFileIsFound As Boolean[/blue]
RecursiveDir colFiles, "C:\", "test1.xlsx", True
[blue]
blnFileIsFound = False[/blue] [green]'Not really needed, default is False[/green]

Dim vFile As Variant
For Each vFile In colFiles
    Debug.Print vFile[blue]
    blnFileIsFound = True[/blue]
Next vFile
[blue]
If Not blnFileIsFound Then
    MsgBox "File Not Found."
End If
[/blue]

Have fun.

---- Andy

 

[jaabaar]

Hi Andy

Thanks its what I want however I get error file not fond and search stops with access. I must be using it wrong: please check as it gives errors:

Error: Error # 53 File not found

The error happens inside the main search routine and stops the search. the file not found should be in the cmd_Search_Loc_Click, as far as I understand it.


Calling the search:

Private Sub cmd_Search_Loc_Click
Dim colFiles As New Collection
Dim vFile As Variant
Dim blnFileIsFound As Boolean
blnFileIsFound = False
[b]Note:[/b] "*ProjectManagement.xlsx" needs to be like that with wild card as the first part of file name is different and 2nd part is always the same ProjectManagement.xlsx.  I am not sure wild card is ok to use with code!

RecursiveDir colFiles, "P:", "*ProjectManagement.xlsx", True

For Each vFile In colFiles
    Debug.Print vFile
    blnFileIsFound = True
Next vFile
If Not blnFileIsFound Then
    MsgBox "File Not Found."
End If
End Sub

The search routine is the same:

Public Function RecursiveDir(colFiles As Collection, _
                             strFolder As String, _
                             strFileSpec As String, _
                             bIncludeSubfolders As Boolean)
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim TEMP
    
    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
     
    Do While strTemp <> vbNullString
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                'Debug.Print "Searching inside folder: " & strTemp
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                 End If
            End If
            strTemp = Dir
        Loop

        'Call RecursiveDir function to seach each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If
End Function

Thank you very much and I really appriciate your advice and help.

 

[jaabaar]

Hi

I have found that it breaks on the foloowing line:
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then

And the folloiwng needs to be in the look as wel:

If Not blnFileIsFound Then
MsgBox "File Not Found."
End If


Sorry for the too much info.
Thank you again very much

 

[Andrzejek]

When your code stops here:

[tt]If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
[/tt]
what are thw values of [tt]strFolder[/tt] and [tt]strTemp[/tt]?

Have fun.

---- Andy

 

[jaabaar]

Hi

The values for:

as you see it has so many nested subfolders

strFolder: P:\ZI0902_Main\Operations Folder\Deployments\Deployment Requests 2009\Completed ICCAT trips\Request_048_Tuna_Princess\Trip Outputs\Deployment_Docs\Training Slides_August 2007\4. Background Longline & Transhipment Operations
As you can see different file type
strTemp = Long_&_Trans.ppt


Hope that helps

 

[Andrzejek]

Just a guess here, but: Long_[red]&[/red]_Trans.ppt is a character that cannot (well, should not) be used in file or folder names, along with [tt]* / \ ( ! ) ?[/tt] etc.

If I am right, you need to include an error handler to capture such problems.

Have fun.

---- Andy

 

[Andrzejek]

Little digging on Google shows this and that which confirms my suspicions... :-(

Have fun.

---- Andy

 

[jaabaar]

Hi Andy
I will change that file name and remove the & and put and to remove the and. Do u think its worth it to add error trapping What about the other stuff.

Andy what do u mean u checked this and that confirms your worry. If that is about me I am in uk and I am currently working just having a bit of problem with the code. I am not stealing code or making u write the code. For me.

I hope I did not offend you in any way.
Thanks for your advice.

 

[Andrzejek]

You have got me wrong, or there is some (small) miss-communication here .
You did not offend me in any way, don’t worry.

My ‘worry’ was about some ‘illegal’ characters in file and folder names, that’s it. Myself, I avoid any ‘unusual’ characters and stick with a-z, A-Z, 0-9, and even avoid spaces, I rather use _ instead. Old school, from DOS days I guess.

Adding the error trapping is ALWAYS a good idea.

“stealing code” is OK in my book. Why re-invent a wheel? Especially code provided on the Net where someone took some time to figure it out and said: “There you go. Use it.” My co-workers ‘still’ my code all the time, I ‘still’ my own code all the time. And I did not write the code I gave you, it is from the Net.

I don’t know what you mean by “What about the other stuff.”


Have fun.

---- Andy

 

[jaabaar]

Thanks will add error trapping when I get the thing working. The the stuff I mean above code section calling the search placed above at 9:58.
If not blnfileisfound then
MSGbox ....
End if

Where do I place it to display it every time file not found like the file found.

Hope that is clear

 

[jaabaar]

I am glad I did not offend u. Miss understanding on my part.

Thanks

 

[jaabaar]

Hi
After extensive debugging the code works very nicely when it finds the excel file and displays each found location path..

My problem now is that it has has to display folder name for each folder found EMPTY as well.

Wild card works very nicely.

Private Sub cmd_Search_Loc_Click
Dim colFiles As New Collection
Dim vFile As Variant
Dim blnFileIsFound As Boolean

blnFileIsFound = False

RecursiveDir colFiles, "P:", "*ProjectManagement.xlsx", True

[COLOR=#3465A4][b] ' Below needs to handle blnFileIsFound = true and false at the same time.  I Cant seem to fix and place inside the for condition.[/b][/color]

For Each vFile In colFiles
    Debug.Print "File Found:" vFile
    blnFileIsFound = True
Next vFile

If Not blnFileIsFound Then
    MsgBox "File Not Found.:" & vFile 
End If

End Sub

The MAIN problem is this part of the code where it only places
strTemp = Dir(strFolder & strFileSpec)

I could adjust to the following but my god it horrible:

if Dir(strFolder & strFileSpec) = "" then[/
[indent]strTemp = strFolder [/indent]
colFiles.Add strFolder 
else
[indent]strTemp = Dir(strFolder & strFileSpec)[/indent]
  Do While strTemp <> vbNullString
    colFiles.Add strFolder & strTemp
    strTemp = Dir
  Loop
end if

And I then need to change the following code here where I have the main problem handling empty folders and adding to collection:


the search routine:

Public Function RecursiveDir(colFiles As Collection, _
                             strFolder As String, _
                             strFileSpec As String, _
                             bIncludeSubfolders As Boolean)
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim TEMP
    
    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
     
    Do While strTemp <> vbNullString
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                'Debug.Print "Searching inside folder: " & strTemp
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                 End If
            End If
            strTemp = Dir
        Loop

        'Call RecursiveDir function to seach each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If
End Function

Please advice to finish as as it is driving me mad.

thanks for all your advice and help.

 

[trevil620]

Giving full credit to Andrzejek for all the code he provided, I think the following may produce what you want.

Here are some of the changes I made:
1. Addedd an error trap to catch some errors if no permission on folder or missing file (I used my c:\ drive as a test).
2. Used the concept of two collections:
(a) colFoundFiles will contain a list of all files found -- including the folder name. If you don't want the folder (which would seem crazy to me), then add code to delete or parse the folder portion.
(b) colEmptyFolders will contain list of any folders where a file was not found.

Just try the following (in a NEW module to preserve the old!!!)

Good Luck,
Wayne
==============================================================

Option Compare Database
Option Explicit

Dim colFoundFiles As Collection
Dim colEmptyFolders As Collection

Private Sub cmd_Search_Loc_Click()
Dim colFiles As New Collection
Dim vFile As Variant
Dim blnFileIsFound As Boolean

Set colFoundFiles = New Collection
Set colEmptyFolders = New Collection

blnFileIsFound = False

RecursiveDir colFiles, "G:\", "*.xls", True

' Below needs to handle blnFileIsFound = true and false at the same time. I Cant seem to fix and place inside the for condition.

Debug.Print "Found colFiles.Count " & colFiles.Count & " files."
Debug.Print "Found colEmptyFolders.Count " & colEmptyFolders.Count & " folders."

'Display list of all files found - including the folder name/
For Each vFile In colFiles
Debug.Print "File Found: "; vFile
blnFileIsFound = True
Next vFile

'Display list of all folders found that did NOT contain files being searched for.
For Each vFile In colEmptyFolders
Debug.Print "Empty Folder: "; vFile
blnFileIsFound = True
Next vFile

If Not blnFileIsFound Then
MsgBox "File Not Found.:" & vFile
End If

End Sub

Public Function RecursiveDir(colFiles As Collection, _
strFolder As String, _
strFileSpec As String, _
bIncludeSubfolders As Boolean)
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim blnFound As Boolean
Dim lItems As Long

On Error GoTo Error_Trap
blnFound = False
Set colFolders = New Collection
If Right(strFolder, 1) = "\" Or Right(strFolder, 1) = "/" Then
'Ignore
Else
colFolders.Add strFolder & "\"
End If

'Add files in strFolder matching strFileSpec to colFiles
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)

Do While strTemp <> vbNullString
colFiles.Add strFolder & strTemp
colFoundFiles.Add strFolder & "|" & strTemp
blnFound = True
strTemp = Dir
Loop

If blnFound = True Then
For lItems = 1 To colFolders.Count
If colFolders(lItems) = strFolder Then
colFolders.Remove (lItems)
End If
Next lItems
Else
colEmptyFolders.Add strFolder
End If

If bIncludeSubfolders Then
'Fill colFolders with list of subdirectories of strFolder
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
'Debug.Print "Searching inside folder: " & strTemp
colFolders.Add strFolder & strTemp
End If
End If
strTemp = Dir
Loop

'Debug.Print "Found " & colFolders.Count & " folders."

'Call RecursiveDir function to seach each subfolder in colFolders
For Each vFolderName In colFolders
Debug.Print "Search: " & vFolderName
strFolder = vFolderName
Call RecursiveDir(colFiles, strFolder, strFileSpec, True)
Next vFolderName
End If

Exit Function
Error_Trap:
Debug.Print Err.Number & vbTab & Err.Description
If Err.Number = 52 Then ' Permissions denied on system folder...
Resume Next
ElseIf Err.Number = 53 Then ' File Not Found
Resume Next
Else
MsgBox "Maybe add code to handle error: " & Err.Number & vbTab & Err.Description
Resume Next
End If
End Function

 

[jaabaar]

Trevil620, thank you very much for the time you have taken to help me with your knowledge. I appreciate it very much. It has help solve my problem. Thank you

 

[jaabaar]

Andrezejek, thank you for taking the time to answer I appreciate it.