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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,702
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,907
Messages
5,489,637
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top