Get filename only

[kaon]

Dear all,

How can I just return the filename only (i.e without path) as an
absolute path can be returned by:
fName = Application.GetOpenFilename(FileFilter:="All files (*.*),
*.*", _
Title:="Select a file",
MultiSelect:=False)

Thanks.

 

[Frank Kabel]

Hi
try the following (will not work in Excel 97 due to InStrRev)
sub foo()
dim fname
fname = application.getopenfilename (FileFilter:="All files (*.*),
*.*", _
Title:="Select a file", MultiSelect:=False)
fname = mid(fname,instrrev(fname,"\")+1,255)
msgbox "You have entered the name:" & fname
end sub

 

[kaon]

Thanks, dude.

I think there is a keyword for that at the beginning

 

[Ron de Bruin]

Hi kaon

For Excel 97 - 2003 you can use this

Sub foo2()
Dim vArr As Variant, fname As Variant
Dim sFileNameXls As String
fname = Application.GetOpenFilename(FileFilter:="All files (*.*),*.*", _
Title:="Select a file", MultiSelect:=False)
vArr = Split97(fname, "\")
sFileNameXls = vArr(UBound(vArr))
MsgBox "You have entered the name:" & sFileNameXls
End Sub

Function Split97(sStr As Variant, sdelim As String) As Variant
'Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi
try the following (will not work in Excel 97 due to InStrRev)
sub foo()
dim fname
fname = application.getopenfilename (FileFilter:="All files (*.*),
*.*", _
Title:="Select a file", MultiSelect:=False)
fname = mid(fname,instrrev(fname,"\")+1,255)
msgbox "You have entered the name:" & fname
end sub

 

[Dick Kusleika]

kaon

You can also use the Dir function

Dir(fname) will return only the file name

 

[Hamilton R. Romano]

Hi

Why not:

fname = dir(fname)

Regards
Hamilton R. Romano

Hi kaon

For Excel 97 - 2003 you can use this

Sub foo2()
Dim vArr As Variant, fname As Variant
Dim sFileNameXls As String
fname = Application.GetOpenFilename(FileFilter:="All files (*.*),*.*", _
Title:="Select a file", MultiSelect:=False)
vArr = Split97(fname, "\")
sFileNameXls = vArr(UBound(vArr))
MsgBox "You have entered the name:" & sFileNameXls
End Sub

Function Split97(sStr As Variant, sdelim As String) As Variant
'Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

 

[ForSale]

http://www.cpearson.com/excel/excelF.htm#FileNam

 

[Ron de Bruin]

Hi Hamilton

When I want to know this a few years back Tom give me this example and
I always use it since then.

But Dir(fname) Is OK for me<g>

Have a nice day

 

[Dave Peterson]

I suggested the dir(fname), too for a poster who was retrieving lots and lots of
files.

Tom suggested instrrev (or some variation). Lots of disk access would slow down
the procedure more than the instrrev (or some variation).

Made sense to me (well, after I was corrected!).

 

[Ron de Bruin]

Hi Dave

Lots of disk access would slow down

Now I can defend myself when I use more code lines<g>