Afternoon all,
Dave has helped to get me this far, I can find a file on c:\ drive, I can isolate the path name etc, my next challenge is to list all the worksheet names (eg Sheet1, Sheet 2 etc) from the file while it is still closed. The code so far is......
Sub FindIt()
Dim filnam
Dim filpatnam
filnam = [a1] ' Determine what file is required
With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Filename = filnam
If .Execute > 0 Then
MsgBox Replace(.FoundFiles(1), filnam, ""), , "BPL"
[a2] = Replace(.FoundFiles(1), filnam, "") & filnam
Else
MsgBox "File Not Found", , "BPL"
End If
End With
' lock down file path and name
filpatnam = Replace(.FoundFiles(1), filnam, "") & filnam
' list the worksheet names
[code to loop thru' file, collect names and copy to cells b1:b'x']
End Sub
Does this make sense?
TIA,
GrahamB