Wish to parse through a text string to find data

[Neil Bhandar]

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

 

[Jake Marx]

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]

 

[J.E. McGimpsey]

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

http://support.microsoft.com/default.aspx?scid=kb;en-us;188007