GetOpenFileName, then display that Filename in a box?

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
439
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

Code:
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!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,684
Office Version
365
Platform
Windows
This should do the trick:
Code:
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

Active Member
Joined
Aug 1, 2018
Messages
439
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,102,369
Messages
5,486,450
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top