GetOpenFileName, then display that Filename in a box?

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top