Leung,
This works for me - it is a bit messy but I use the countmychar function
elsewhere.

HTH

Andi

Try :-
Sub Test
Dim workfilePath As String
Dim val_countPos as Integer
Dim Workfile as string
workfilePath = " F:\FC_bank\_common\Month-end Jun 05\MEJun2005\SAP\RC
code 13Jul2005.xls"
val_countpos = CountMyChar(workfilePath, 1, "B", "\") + 1
workfile = Mid(workfilePath, val_countpos) 'extract filename from
WorkfilePath
msgbox (workfile)
End Sub

Function CountMyChar(strCSV As String, ItemNo As Integer, FrontOrBack As
String, _
MySeparator As String) As String
'Function counts position of character within string - can count from front
or back
Dim i As Long
Dim OneItem As String
Dim n As Integer
Dim Var_CharCount As Integer
Dim Var_NumCharCount As Integer 'n'th item to find position of

Var_CharCount = 0 'current count of Item is 0

If UCase(FrontOrBack) = "F" Then
MySt = 1
MyFin = Len(strCSV)
MyStep = 1
Else
MySt = Len(strCSV)
MyFin = 1
MyStep = -1
End If

For n = MySt To MyFin Step MyStep
char = Mid(strCSV, n, 1)

If char = MySeparator Then
Var_NumCharCount = Var_NumCharCount + 1
End If

If Var_NumCharCount = ItemNo Then
Exit For
End If

Next n

CountMyChar = n

End Function



"Leung" wrote in message
...
Hi

Is there anyway use some text extract function in VBA so that I can
retrieve
the file name from these path? How to write a code in a function to
retrieve
the name:

such as intri function??
path:
F:\FC_bank\_common\Month-end Jun 05\MEJun2005\SAP\RC code 13Jul2005.xls
string to retrieve:
RC code 13Jul2005.xls

thanks

Leung