I need to parse the file name (e.g. MyFileName.xls) from a text string
containing the full path and file name. Surely there is an easy way to do
this. Please help me out!

Public Sub parse_filename()
'Alternatively you could use this command to get JUST the filename:
' FilName= Application.ActiveWorkbook.Name 'This returns "Myxlbook.XLS"

Filname = Application.ActiveWorkbook.FullName 'This returns "C:\Documents
and Settings\User\Myxlbook.XLS

For I = Len(Filname) To 1 Step -1
If Mid(Filname, I, 1) = "\" Then
Filname = Right(Application.ActiveWorkbook.FullName, Len(Filname) - I)
Exit For
End If
Next
Debug.Print Filname

End Sub

"Mitch" wrote in message
...
I need to parse the file name (e.g. MyFileName.xls) from a text string
containing the full path and file name. Surely there is an easy way to do
this. Please help me out!

Using some basic text manipulation functions it is not too bad to do.

Sub test()
MsgBox GetFileName("C:\Test\Test\this.xls")
End Sub

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

--
HTH...

Jim Thomlinson


"Mitch" wrote:

I need to parse the file name (e.g. MyFileName.xls) from a text string
containing the full path and file name. Surely there is an easy way to do
this. Please help me out!

This is one way:

Public 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


"Mitch" wrote in message
...
I need to parse the file name (e.g. MyFileName.xls) from a text string
containing the full path and file name. Surely there is an easy way to do
this. Please help me out!

Mitch wrote:
I need to parse the file name (e.g. MyFileName.xls) from a text string
containing the full path and file name. Surely there is an easy way
to do this. Please help me out!

Another way:

sFileName = Dir(sPath)

as long as the file actually exists.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Sorry I found this post already exists with an answer..

Helps if you spell correctly when doing a search!


--
Ray


"Dick Kusleika" wrote:

Mitch wrote:
I need to parse the file name (e.g. MyFileName.xls) from a text string
containing the full path and file name. Surely there is an easy way
to do this. Please help me out!

Another way:

sFileName = Dir(sPath)

as long as the file actually exists.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com