Function to return filepath given full filename

[Guest]

I need a function (whether built in or user defined) that can return the filepath (ie C:\mypath ) given the full filename with path ( as returned in the GetOpenFileName function, ie C:\mypath\myfile.xls )

Any help would be greatly appreciated

Matt Lawso

 

[Guest]

ANy suggestions to improve this function

(I thought about it for a while and came up with a solution

Function ReturnPath(sFname
Dim spath As String, i As Long, leng As Intege
leng = Len(sFname
For i = 1 To leng -
If Mid(sFname, leng - i, 1) = "\" The
spath = Mid(sFname, 1, leng - i - 1
Exit Fo
End I
Nex

ReturnPath = spat

End Function

 

[Tom Ogilvy]

Public Function sPath(sStr As String)
If InStr(sStr, "\") = 0 Then
sPath = ""
Else
i = Len(sStr)
Do While i > 0
If Mid(sStr, i, 1) = "\" Then
sPath = Left(sStr, i )
Exit Do
End If
i = i - 1
Loop
End If
End Function


If you have excel 2000 or later, you can use split to get the filename
sStr = "C:\Myfolder1\myfolder2\myfile.xls"
v = (sStr,"\")
sPath = Left(sStr,len(sStr)-v(ubound(v)))

--
Regards,
Tom Ogilvy

I need a function (whether built in or user defined) that can return the

filepath (ie C:\mypath ) given the full filename with path ( as returned in
the GetOpenFileName function, ie C:\mypath\myfile.xls ).

 

[Vasant Nanavati]

Try:

Left(FullFileName, Len(FullFileName)-Len(Dir(FullFileName)) - 1)

Assumes this is an existing file on the user's computer.

(Credit to Ivan F. Moala!)


--

Vasant

I need a function (whether built in or user defined) that can return the

filepath (ie C:\mypath ) given the full filename with path ( as returned in
the GetOpenFileName function, ie C:\mypath\myfile.xls ).

 

[Harlan Grove]

If you have excel 2000 or later, you can use split to get the filename
sStr = "C:\Myfolder1\myfolder2\myfile.xls"
v = (sStr,"\")
sPath = Left(sStr,len(sStr)-v(ubound(v)))

....

If OP has XL2K or later, OP could use InStrRev.

p = InStrRev(sStr, "\")
sPath = IIf(p > 0, Mid(sStr, p + 1), "")

 

[Dave Peterson]

Just a correction of typos:

Dim v As Variant
sStr = "C:\Myfolder1\myfolder2\myfile.xls"
v = Split(sStr, "\")
sPath = Left(sStr, Len(sStr) - Len(v(UBound(v))))
'MsgBox sPath

 

[Tom Ogilvy]

Bunch of typos on that - guess I shouldn't attempt an xl2000 solution if I
only have xl97 on that machine <g>. Thanks for the correction.

 

[Tom Ogilvy]

Excellent suggestion - but as written that gives the filename. For the path
I think you meant:

p = InStrRev(sStr, "\")
sPath = IIf(p > 0, Left(sStr, p), "")

 

[Dave Peterson]

Doesn't stop me for posting xl97 solutions, er, suggestions with only xl2002
available <vvbg>.

Bunch of typos on that - guess I shouldn't attempt an xl2000 solution if I
only have xl97 on that machine <g>. Thanks for the correction.

<<snipped>>