CODEWITHSUNDEEP
vbaexcel
Dingo
Dingo

Reputation: 123

InSTR or find function in VBA

I am stuck with an issue. I've done my research and found out that I can use InSTR function to search for a specific character in a string.

What I am trying to do is get a file name extracted from a file path.

Currently I have

  InStr(StrFrom(pName), "\")

The issue here is, it returns the first occurrence of slash, where as I want to get the last occurrence of the slash (so that I can use a 'right' function wrapped around the above code to capture the file name)

Any help is appreciated on how to get the last slash in a string!

Thanks!

Upvotes: 2

Views: 9130

Answers (4)

Guillermo Phillips
Guillermo Phillips

Reputation: 2216

Assuming StrFrom is some user defined function, the following will do what you want:

Dim filename as String
Dim path as String

path = StrFrom(pName)
filename = Mid$(path, InstrRev(path, "\") + 1)

Note that its easier to use Mid$ than Right$, as InstrRev returns the character position from the left of the string. Omitting the final parameter of Mid$, returns the rest of the string from that position.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub marine()
    Dim s As String, ary
    s = "C:\whatever\sub1\sub2\reallydeep\x.xlsm"
    ary = Split(s, "\")
    MsgBox ary(UBound(ary))
End Sub

enter image description here

Upvotes: 3

Dave Cripps
Dave Cripps

Reputation: 929

Use InStrRev() to find the first occurrence of the slash from the right side of the string.

https://msdn.microsoft.com/en-us/library/t2ekk41a(v=vs.90).aspx

Upvotes: 2

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

Instr looks from the start of the text string, InstrRev starts looking from the other end.

Public Function FileNameOnly(ByVal FileNameAndPath As String) As String

    FileNameOnly = Mid(FileNameAndPath, InStrRev(FileNameAndPath, "\") + 1, Len(FileNameAndPath))

End Function

Upvotes: 9

Related Questions