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)
    ExtractFileName = sTempName
End Function

Posted in Uncategorized

7 thoughts on “ExtractFileName

  1. 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

  2. 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.

  3. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.