Extract full path given filename

[Kim J.]

Is there a way to extract the full file path into a
message box given just the file name? For example, if I
entered "Testfile.xls" into an input box, I would like
Excel to go out and find the path. It would then return a
message box that says something like:

"c:\programs\excel\myfiles\friday\Testfile.xls"

I've tried . . .

Sub FindPath()
Dim MyFile As String
MyFile = ActiveWorkbook.FullName
MsgBox MyFile
End Sub

but it only works for the active workbook. I would like
to enter any file name and have it return the correct path.

 

[Frank Kabel]

Hi
do you mean you want your code to search the entire harddisc for this
file?

 

[Trevor Shuttleworth]

Kim

I guess one way would be:

Windows("Testfile.xls").Activate
Debug.Print ActiveWorkbook.Path
Debug.Print ActiveWorkbook.Name
Debug.Print ActiveWorkbook.FullName
ThisWorkbook.Activate

Regards

Trevor

 

[David McRitchie]

Hi Kim,
Excel can have only one workbook open with a given filename,
and you could efficiently provide the full pathname for that (I'm sure),
but you want Excel to search your entire hard drive, for filenames
holding up Excel for a search that could take a while and could
produce more than one file. So I think you need some rethinking.
On a network drive you'd kind of be hit and miss as you would only
have access to your files that you are allowed to see the filenames.