Get filename only

K

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.
 
F

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
 
R

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


Frank Kabel said:
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
Click to expand...
 
D

Dick Kusleika

kaon

You can also use the Dir function

Dir(fname) will return only the file name
 
H

Hamilton R. Romano

Hi

Why not:

fname = dir(fname)

Regards
Hamilton R. Romano

Ron de Bruin said:
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
Click to expand...
 
R

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
 
D

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!).
 
Top