Hello:

I have string that looks like:

c:\temp\TstDir\Excel.xls

I wish to parse through & dif out the name of the file:
Excel.xls; Dir: TstDir

Is there an easy way to accomplish this?

Thanks,
-Neil

Hi Neil,

There are a few ways to do this:

Sub test()
Dim s As String
Dim nPos As Integer
Dim nPos2 As Integer

s = "c:\temp\TstDir\Excel.xls"

nPos = InStrRev(s, Application.PathSeparator)
nPos2 = InStrRev(s, Application.PathSeparator, nPos - 1)
Debug.Print "Filename: " & Mid$(s, nPos + 1)
Debug.Print "Folder: " & Mid$(s, nPos2 + 1, nPos - nPos2 - 1)
End Sub

Sub test2()
Dim s As String
Dim v As Variant
Dim n As Integer

s = "c:\temp\TstDir\Excel.xls"
v = Split(s, Application.PathSeparator)
n = UBound(v)

Debug.Print "Filename: " & v(n)
Debug.Print "Folder: " & v(n - 1)
End Sub


Both of these methods require Excel 2000 or later. If you are on 97 or
earlier, you'll have to parse through the string from beginning to end using
InStr() or Mid$().

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Neil Bhandar wrote:
Hello:

I have string that looks like:

c:\temp\TstDir\Excel.xls

I wish to parse through & dif out the name of the file:
Excel.xls; Dir: TstDir

Is there an easy way to accomplish this?

Thanks,
-Neil

Or roll your own InStrRev UDF. Some non-optimized VBA5 equivalents
of VBA6 string functions are listed at

http://support.microsoft.com/default...b;en-us;188007


In article ,
"Jake Marx" wrote:

Both of these methods require Excel 2000 or later. If you are on 97 or
earlier, you'll have to parse through the string from beginning to end using
InStr() or Mid$().