Extract full path given filename
 

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.

Extract full path given filename
 

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.

Extract full path given filename
 

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.

Extract full path given filename
 

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.