Search various folders for a file.

[PALman]

I have recently been shown how to code a button to find and display any document. I now need to search a number of folders. For example... The documents filenames will always start with a JobNo like 226488 and would be found in the folder named 226000-226500. However to cover any JobNo, I need the search to begin at the folder above this which would be named Completed Jobs. So the file's full path would read "C:\Jobs\Completed Jobs\226000-226500\226488.doc".
Any help again much appreciated.
PALman

 

[abaldwin]

What is the code that you are looking to modify?


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[PALman]

As this is a new project, I thought I could start with fresh coding, however the present coding searches one particular folder which is stored in the Path field of a table called Folder Paths is as follows...

Dim strFile As String, i As Integer
Dim Path

Debug.Print "JobNo='" & Me!JobNo & "'"
Path = DLookup("[Path]", "[Folder Paths]", "[PathNo] = 'Path04'")
'MsgBox Path
strFile = Dir(Path & Me!JobNo & "-*.*")

Do While strFile <> ""
i = i + 1
Debug.Print i, strFile
FollowHyperlink Path & strFile
strFile = Dir
Loop
Thanks

 

[PHV]

Why not using the FileSearch object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Could you please give an example of coding using FileSearch object?

 

[PALman]

The code below finds files by JobNo in ONE particular folder/path where the path is stored in a field of a table called "Folder Paths. I need to extend this this code to search a folder AND its sub-folders. It has been sugested by PHV that I use FileSearch but I am not sure how to use this within my present code...

Debug.Print "JobNo='" & Me!JobNo & "'"
Path = DLookup("[Path]", "[Folder Paths]", "[PathNo] = 'Path04'")
strFile = Dir(Path & Me!JobNo & "-*.*")
Do While strFile <> ""
i = i + 1
Debug.Print i, strFile
FollowHyperlink Path & strFile
strFile = Dir
Loop

Any help much appreciated.

 

[JerryKlmns]

Typed not tested

Function myFileSearching(ByVal File_Name As String, ByVal Search_Folder As String) () As String
Dim iCount As Long, iStop As Long
With Application.FileSearch
    .NewSearch
    .FileType = 1 'msoFileTypeAllFiles
    .Filename = File_Name & "*.*"
    .LookIn = Search_Folder
    .SearchSubFolders = True
    If .Execute(4, 1, True) > 0 Then 
        iStop= .FoundFiles.Count - 1
        ReDim myFileSearching(iStop)
        For iCount = 0 To iStop
            myFileSearching(iCount)= .FoundFiles(iCount)
        Next iCount
    Else
        MsgBox "Searching for files " & File_Name & " end up with NO FILE located in folder " & Search_Folder & " and its subfolders")
    End If
End With
End Function

Dim myArr() As String
Dim myCount As Long

Debug.Print "JobNo='" & Me!JobNo & "'"
Path = DLookup("[Path]", "[Folder Paths]", "[PathNo] = 'Path04'")
myArr = myFileSearching (Me!JobNo & "-", Path )
For myCount = LBound(myArr) To UBound(myArr)
   Debug.Print myCount, myArr(myCount)
   FollowHyperlink myArr(myCount)
Next

 

[PALman]

Hi JerryKlmns
Sorry for delay in trying your code.
The first line gives a syntax error...
Function myFileSearching(ByVal File_Name As String, ByVal Search_Folder As String) () As String
Also could you explain the two groups of coding. Does the first (a Function) need to be copied into a special area of the editor with the second coding going behind the control button? Sorry I haven't used Function before.
As I need to be able to search a number of folders and sub-folder to find files any further help is much appreciated.

 

[PHV]

Could you please give an example of coding using FileSearch object?|/i]
No F1 key on your keyboard ?

 

[PALman]

NO - Helpfile is not installed.

 

[PHV]

So, install it !
This is MANDATORY if you want to play with VBA, trust me.

 

[PALman]

Don't have administrator rights and he's on holiday!

 

[PHV]

Have a look here:

http://msdn.microsoft.com/library/en-us/vbaof11/html/ofobjFileSearch1_HV05221253.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Thanks again PHV.
I am trying the example found in the link you sent. Not being a programmer it's sometimes difficult to get my head around some coding. Thanks

 

[PALman]

Can anyone tell me how the following code always searches for files in "My Documents"
With Application.FileSearch
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Then explain how the next coding produces the error "Invalid Procedure Call or Argument. It is this next piece of coding I need to use so that I can set the path in .Lookin but I am having some difficulty in making it work.

With Application.FileSearch
.NewSearch
.LookIn = "C:\000-Quality Control Program\"
.SearchSubFolders = True
.FileName = "Run"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
End With

Further help is very much appreciated.

 

[JerryKlmns]

PALman

About the first, the code uses the default value for LookIn property, so it should use My Documents folder.

For the second part, if you don't have a reference to Microsoft Office x.0 Object Library, then the FileType enumerator is not defined... the value of which is 1, in case you don't want to reference that library!

 

[PALman]

Thanks JerryKlmns,
I shall use your info to continue to reach my objective of writing code to find a file using DLookup and searching through a Folder and it's Sub-Folders.
Thanks again.

 

[PALman]

Can anyone help.
The following code always looks in "My Documents" as a default folder when trying to find a file.
I need to change this to lookin a particular folder and be able to search down through sub-directories/folders.

With Application.FileSearch
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
Any help appreciated

 

[JerryKlmns]

PALman

You asked the same thing on Aug 14th and I gave you an answer. Have you tried that?

 

[PALman]

Hi JerryKlmns
Yes I have tried what you suggestd but still can't find a way to change the default "My Documents" folder.
I have reference to Microsoft DAO 3.6 Object Library and Microsoft Access 10.0 Object Library and Micerosoft Forms 2.0 Object Library and Microsoft Office 10 Object Library.
This is a very grey area for me and I do not know how to change the FileTpe numerator if I had to.
However I think my main problem is the first one where I need to change default folder. If I can do this I think I could finish this.
Thanks for your help.