Searching fro A file and returming the pathe to that file

[QuietMan]

I trying to search a specified directory for the location of a specific file
Once the file is found I want to be able to return that directory and path
so i can use it in other parts of of the macro

Thanks

 

[Joel]

filesearch can be used, but I was told it doesn't work in excel 2007. Here
is a method that will work. I assume that you need to search subdirectories.

Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "xyz.txt"
'directory to start searching
strFolder = "c:\temp"
file_loc = ""
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count > 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", MyFileName)
If file_loc <> "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If file_loc = "" Then
For Each fl In folder.Files
If fl.Name = MyFileName Then
file_loc = folder.Name
End If
Next fl
End If
200 On Error GoTo 0

End Sub

 

[Don]

Take a look here....Conan just helped me with a similar request. I'm using
2003 and it works great.

http://www.microsoft.com/office/com...&p=1&tid=976eb7c5-5b7e-4641-b23a-b2d2523a7cf2

HTH,

Don