Hi
here is the code:
here is the code:
Code:
Sub findfile()
Dim FoundIt As Boolean
Dim FileToGet As String
FoundIt = False
FileToGet = "hello.xls" 'can be part or all of a file
If Len(FileToGet) = 0 Then Exit Sub
Set fs = CreateObject("scripting.filesystemobject")
For Each dr In fs.Drives
Application.StatusBar = "Searching " & dr
If Not FoundIt Then
With Application.FileSearch
.LookIn = dr
.SearchSubFolders = True
.Filename = FileToGet
.Execute
If .FoundFiles.Count > 0 Then
For i = 0 To .FoundFiles.Count - 1
Range("order!ea1").Formula = WorksheetFunction.Substitute(UCase(.FoundFiles.Item(i + 1)), UCase(.Filename), "")
FoundIt = True
Next i
End If
End With
End If
Next dr
If Not FoundIt Then Range("order!eA1").Formula = "Not Found"
Application.StatusBar = False
End Sub