Function ExtractFileName(sFullPath As String) As String
‘Returns the filename from a full path
Dim lSepPos As Long
Dim sTempName As String
Const sPATHSEP As String = “”
sTempName = sFullPath
lSepPos = InStr(1, sFullPath, sPATHSEP)
Do Until lSepPos = 0
sTempName = Right(sFullPath, Len(sFullPath) – lSepPos)
lSepPos = InStr(lSepPos + 1, sFullPath, sPATHSEP)
Loop
ExtractFileName = sTempName
End Function
7 thoughts on “ExtractFileName”
Posting code? Use <pre> tags for VBA and <code> tags for inline.
I posted something similar in VBAExpress, here.
Link
Using Split is pure genius. Now why didn’t I think of that?
Might want to make use of Application.PathSeparator
Hi,
Forgive the late post – I just happened to be browsing through some of the code here (there’s some good stuff!).
Is there any reason why we can’t simply use Dir?
Like this:
Sub Test()
Dim strFullName As String
strFullName = ThisWorkbook.FullName
MsgBox “The fullname is : ” & strFullName & vbNewLine & _
“The filename is : ” & Dir(strFullName)
End Sub
Richie: Because the file would have to exist for Dir to work. There may be a case that you need to parse a string that looks like a path and file, but that actually isn’t a file on the hard drive. Pretty rare, I know, but that was the thinking.
Aahhh … I see. Cheers.
Hi there – I cpied the code and came up with a couple of problems which I have managed to fix.
Firstly the single quote at the start of the line
?eturns the filename from a full path
needs to be replaced with a single quote like this one ‘ to make it a comment.
Second the line
Const sPATHSEP As String = ?
needs to be amended as follows
Const sPATHSEP As String = “”
Not sure why this corruption is occurring but my comments may help someone else.
Regards David