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

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


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



"Frank" wrote in message ...
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

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

Jim Thomlinson


"Frank" wrote:

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

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


"Frank" wrote in message
...
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

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.



Frank wrote:

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

--

Dave Peterson

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

--
Cheers
Nigel



"Dave Peterson" wrote in message
...
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.



Frank wrote:

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

--

Dave Peterson