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 — LimaNightHawk
– LimaNightHawk, Commented Nov 11, 2016 at 17:03

Darren Bartrup-Cook · Accepted Answer · 2016-11-11 16:59:27Z

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, 2016 at 16:59

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dingo Over a year ago

Thanks all - I've used a combination about instrRev + above logic to get the name :)

Darren Bartrup-Cook Over a year ago

Was going to add

MsgBox CreateObject("Scripting.FileSystemObject").GetFileName(CreateObject("Scripting.FileSystemObject").GetAbsolutePathName("<Full Path To File>"))

as a one liner - not particularly useful though (calls the FileSystemObject twice). :)

user3598756 Over a year ago

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 -

Darren Bartrup-Cook Over a year ago

Application.PathSeparator - that was the command I was trying to remember. Couldn't think of the wording to save my life. :)

Gary's Student · Accepted Answer · 2016-11-11 17:01:52Z

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, 2016 at 17:01

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Dave Cripps · Accepted Answer · 2016-11-11 17:00:09Z

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, 2016 at 17:00

Dave Cripps

9297 silver badges11 bronze badges

Comments

Guillermo Phillips · Accepted Answer · 2016-11-11 17:05:44Z

1

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.

answered Nov 11, 2016 at 17:05

Guillermo Phillips

2,2261 gold badge24 silver badges44 bronze badges

Collectives™ on Stack Overflow

InSTR or find function in VBA

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related