The path of a file found in a subdirectory using Filesearch...
Posted by Ben O. on October 05, 2000 8:15 AM
I'm using Application.Filesearch and I'd like to be able to search the subfolders of the search path that I specify. When I use .SearchSubFolders = True, problems arise when the file is found in a subfolder, because I need to link the file later. This is what I use to change the link:
With Application.FileSearch
.NewSearch
.LookIn = Searchpath
.SearchSubFolders = True
.Filename = "Target.xls"
.MatchTextExactly = True
.FileType = xlExcelLinks
ActiveWorkbook.Changelink Name:="OldFilename.xls", NewName:=Searchpath & .Filename
The problem is that if the file was found in a subfolder of Searchpath, using Searchpath & .Filename as the name of the new link won't be accurate. I need to know the path that .Filename was found in. Is this possible?
-Ben
Posted by Steve on October 06, 2000 5:13 AM
Your question helped me solve a little problem I was having. I researched it a bit, and I think this should help you:
sub FindFile
Dim index As Integer
With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.SearchSubFolders = True
.FileName = "Personnel.xls"
.MatchTextExactly = True
.FileType = xlExcelLinks
If .Execute() > 0 Then
For index = 1 To .FoundFiles.Count
MsgBox .FoundFiles(index)
'(returns full pathname)
Next
Else
MsgBox .FileName & " Not found"
End If
MsgBox .LookIn
End With
End Sub
Good luck,
Steve
Posted by Ben O. on October 06, 2000 2:32 PM
Thanks for your help Steve. But I think that VBA might act differently when it assigns a link the name of the found file than when it diplays the found file's path in a message box. When I make .FoundFiles(index) the new link name, it gives me a file not found error when the file found by FileSearch is in a subfolder. Ah well, it was a long shot anyway!
-Ben
