extract filename from full path

[Guest]

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

 

[NickHK]

Leung,
Look into InStrRev or Split functions.

NickHK

 

[Andibevan]

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

 

[Bob Phillips]

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

 

[Harald Staff]

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

 

[Michel Pierron]

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

MP

 

[Bob Phillips]

Sorry, meant Name.

--
HTH

Bob Phillips

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

 

[Guest]

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