Taking the last part of a string from the right 2

[Molby]

I'm trying to get the last part of a filename from the last "\" character. As an example I have:

"\\servername\folder1\folder2\folder3\filename.xls" All I want is the bit "filname.xls". The problem is that the filename length will vary, so I was trying to use Right, but I wont know how many characters to use. If I use too many I will end up with "der3\filename.xls". I thought maybe using Instr somehow, but I can't think of how to find the last occurance of "\". Any ideas?

 

[MatthewsP]

You were almost there. Use the InStrRev function:

Function FixFileName(PathString As String)

Dim LastBackslashPos As Integer

LastBackslashPos = InStrRev(PathString, "\&quot

If LastBackslashPos <= 0 Then
FixFileName = CVErr(xlErrNA)
Exit Function
End If

FixFileName = Right(PathString, Len(PathString) - LastBackslashPos)

End Function

Cheers!

 

[combo]

Use InStrRev, searches the string from the end.

combo

 

[Molby]

Excellent, thanks for that guys stars for you both.

 

[Tranman]

Molby,
Try this:
strFullPath is the complete UNC file name
strFileName is where just the file name ends up.

strFileName = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "\&quot)

Tranman

 

[combo]

Thanks Molby.
Another way:
Filename = Split(FullName, "\&quot(UBound(Split(FullName, "\&quot))

combo

 

[PHV]

If InStrRev is not available in your VBA version (as for Office97), you may consider to use the Split function:

a=Split("\\servername\folder1\folder2\folder3\filename.xls","\")
MsgBox a(UBound(a))

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Molby]

All very useful stuff. Thanks for all the replies. I've used the original method and it works just how I wanted it.