InSTR or find function in VBA

Question

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!

Possible duplicate of [Check if a string contains another string](http://stackoverflow.com/questions/15585058/check-if-a-string-contains-another-string) — LimaNightHawk, Nov 11 '16 at 17:03

score 9 · Accepted Answer · answered Nov 11 '16 at 16:59

9

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

answered Nov 11 '16 at 16:59

Darren Bartrup-Cook

18,362
1
23
45

Thanks all - I've used a combination about instrRev + above logic to get the name :) – Dingo Nov 11 '16 at 17:04
Was going to add `MsgBox CreateObject("Scripting.FileSystemObject").GetFileName(CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(""))` as a one liner - not particularly useful though (calls the FileSystemObject twice). :) – Darren Bartrup-Cook Nov 11 '16 at 17:12
both fine solutions to which I'd add 1) use `Application.PathSeparator` instead of `"\"` and give it more generality 2) avoid double FileSystemObject objects `With CreateObject "Scripting.FileSystemObject")| FileNameOnly = .GetFileName(.GetAbsolutePathName(FileNameAndPath))| End With -` – user3598756 Nov 12 '16 at 07:04
`Application.PathSeparator` - that was the command I was trying to remember. Couldn't think of the wording to save my life. :) – Darren Bartrup-Cook Nov 14 '16 at 08:53

score 3 · Answer 2 · answered Nov 11 '16 at 17:01

3

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

answered Nov 11 '16 at 17:01

Gary's Student

95,722
10
59
99

score 2 · Answer 3 · answered Nov 11 '16 at 17:00

2

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

answered Nov 11 '16 at 17:00

Dave Cripps

929
7
11

score 1 · Answer 4 · answered Nov 11 '16 at 17:05

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.

InSTR or find function in VBA

4 Answers4