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

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

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




"Matt Lawson" wrote in message
...
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 Lawson

"Tom Ogilvy" wrote...
....
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), "")

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), "")


--
Regards,
Tom Ogilvy

"Harlan Grove" wrote in message
...
"Tom Ogilvy" wrote...
...
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), "")

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 wrote:

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

"Matt Lawson" wrote in message
...
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 Lawson



--

Dave Peterson

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
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 wrote:

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

"Matt Lawson" wrote in message
...
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 Lawson



--

Dave Peterson

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

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy


<<snipped


--

Dave Peterson

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





"Matt Lawson" wrote in message
...
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 Lawson