ExtractFileName
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
Juan Pablo:
I posted something similar in VBAExpress, here.
Link
26 May 2004, 10:29 amDick:
Using Split is pure genius. Now why didn’t I think of that?
26 May 2004, 10:37 amRob van Gelder:
Might want to make use of Application.PathSeparator
30 June 2004, 10:46 pmRichie(UK):
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
31 January 2005, 6:58 amDick:
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.
31 January 2005, 9:02 amRichie(UK):
Aahhh … I see. Cheers.
31 January 2005, 2:55 pmDavidB:
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
‘Returns 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
2 September 2005, 11:37 pm