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

Leung,
Look into InStrRev or Split functions.

NickHK

"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

If the string refers to an open workbook, you could use the Path property of
that workbook.

--
HTH

Bob Phillips

"NickHK" wrote in message
...
Leung,
Look into InStrRev or Split functions.

NickHK

"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

Sorry, meant Name.

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
If the string refers to an open workbook, you could use the Path property
of
that workbook.

--
HTH

Bob Phillips

"NickHK" wrote in message
...
Leung,
Look into InStrRev or Split functions.

NickHK

"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

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

Hi Leung

Sub Test()
MsgBox sFileName("F:\FC_bank\_common\Month-end " & _
"Jun 05\MEJun2005\SAP\RC code 13Jul2005.xls")
End Sub

Function sFileName(sFullname As String) As String
If InStrRev(sFullname, "\") = 0 Then
sFileName = sFullname
Else
sFileName = Mid$(sFullname, _
InStrRev(sFullname, "\") + 1)
End If
End Function

HTH. Best wishes Harald

"Leung" skrev i melding
...
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

Hi Leung,
Try:
FileName = Dir(F:\FC_bank\_common\Month-end Jun 05\MEJun2005\SAP\RC code
13Jul2005.xls)

MP

"Leung" a écrit dans le message de news:
...
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

I hope this work for you:

Sub Trial()
ActiveCell = fcnOnlyFileNameFromFullname("C:\My documents\My file.xls")
End Sub

Function fcnOnlyFileNameFromFullname(FullName As String) As String
Dim i As Integer
i = 0
Do Until Left$(Right$(FullName, i), 1) = "\"
i = i + 1
Loop
fcnOnlyFileNameFromFullname = Right$(FullName, i - 1)
End Function

--
Sincerilly, thanks a lot.

Oscar Picos
Office Autom


"Leung" wrote:

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