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.

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

--
Regards
Frank Kabel
Frankfurt, Germany


Kim J. wrote:
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.

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


"Kim J." wrote in message
...
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.

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Frank Kabel" wrote in message ...
Hi
do you mean you want your code to search the entire harddisc for this
file?

--
Regards
Frank Kabel
Frankfurt, Germany


Kim J. wrote:
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.