How to split filename from filepath?

[Guest]

If I retrieve a filename including the filepath like this:

strFileName = Application.GetOpenFilename("Report (*.txt; *.dat ),*.txt;
*.dat")

How do I manipulate the strFileName, retrieving only the filename?

(guess I have to search from right to find the first "\". Then delete the
content to the left of this character. But I don't know how this works in VBA)

Thanks for suggestions.

Frank

 

[Ron de Bruin]

Hi Frank

You can use dir and use the left function to remove the extension

Sub test()
Dim FName As Variant
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = ThisWorkbook.Path
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
MsgBox Dir(FName)
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub

 

[Guest]

I usually impliment it as a function...

Public Function GetFileName(ByVal FullName As String) As String
GetFileName = Right(FullName, Len(FullName) - InStrRev(FullName, "\"))
End Function

 

[RB Smissaert]

Something like this will do it:

Function FileFromPath(ByVal strFullPath As String, _
Optional bExtensionOff As Boolean = False) As String

Dim FPL As Long 'len of full path
Dim PLS As Long 'position of last slash
Dim pd As Long 'position of dot before exension
Dim strFile As String

On Error GoTo ERROROUT

FPL = Len(strFullPath)
PLS = InStrRev(strFullPath, "\", , vbBinaryCompare)
strFile = Right$(strFullPath, FPL - PLS)

If bExtensionOff = False Then
FileFromPath = strFile
Else
pd = InStr(1, strFile, ".", vbBinaryCompare)
FileFromPath = Left$(strFile, pd - 1)
End If

Exit Function
ERROROUT:

On Error GoTo 0
FileFromPath = ""

End Function


RBS

 

[Dave Peterson]

One more way:

Option Explicit
Sub testme()
Dim strFileName As String
Dim mySplit As Variant
strFileName = "c:\somepath\anotherpath\filename.xls"

mySplit = Split(strFileName, "\")
'mySplit = Split97(strFileName, "\")
MsgBox mySplit(UBound(mySplit))

End Sub

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


Split was added in xl2k. If you're using xl97, use Tom's split97. If you and
your users are all at xl2k or higher, you can delete that function completely.

 

[Nigel]

Also note that InStrRev is not implimented in xl97 so use Dave Peterson /
Tom Olgivy methods