GetOpenFileName, then display that Filename in a box?

[RockandGrohl]

Hi all, have a box with a button next to it, the idea is, user clicks the button which opens the directory where the Mailing Lists are stored, then the user selects the Mailing List and it stores the path + filename as "facml"

I then want the box to fill in *just the file name*

Here's my code below

ChDir "\\chw-dc03\company\Sales\Faculty Documents\2019 Mailing Lists"facml = Application.GetOpenFilename(Title:="Please choose a Faculty Mailing List")
If facml = False Then
Exit Sub
End If
Application.AskToUpdateLinks = True


Range("D4").Value = facml

What's happening right now is it's giving me the whole path + filename, I just need the filename to go in the box, thanks!

 

[sykes]

This should do the trick:

Dim str As String

ChDir "\\chw-dc03\company\Sales\Faculty Documents\2019 Mailing Lists"
facml = Application.GetOpenFilename(Title:="Please choose a Faculty Mailing List")
If facml = False Then Exit Sub
Application.AskToUpdateLinks = True

str = Right(facml, Len(facml) - InStrRev(facml, "\", , 1))
Range("D4").Value = str

 

[RockandGrohl]

Ah that's a great shout, well done and thanks.

 

[sykes]

Pleasure.

 

[RockandGrohl]

Pleasure.

Got another one for you..

The string I now need to extract is after the last "" and before the first instance of "Mailing_" but, the "Mailing_" needs to be wrapped in UCase as there are instances where it's either mailing or Mailing.

I've been trying Mid with no results, but the syntax is lost on me

Thanks


Ah, kinda got there in a weird way with this:

spos = InStr(1, facml, "ists\") + 5epos = InStr(spos, facml, "ailing_") - 2
camnam = Mid(facml, spos, epos - spos)