Wish to parse through a text string to find data

  • Thread starter Thread starter Neil Bhandar
  • Start date Start date
N

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
 
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]
 
Back
Top