Option Explicit 
 
 'Enumeration for what to return
Public Enum PathReturn 
    GetPath = 1 
    GetFile = 0 
End Enum 
 
Public Function StripFileOrPath(FullPath As String, _ 
    ReturnType As PathReturn) As String 
     '   =====================================================================
     '   Returns either the FileName or the Path from a given Full FileName
     '   1st Arg = Pass a files full name (C:\Example\MyFile.xls)
     '   2nd Arg = What to return (either the file name or the path
     '             Enumerated for easier selection
     '   =====================================================================
    Dim szPathSep As String 
    szPathSep = Application.PathSeparator 
     
    Dim szCut As String 
    szCut = CStr(Empty) 
     
    Dim i As Long 
    i = Len(FullPath) 
     
    Dim szPath As String 
    Dim szFile As String 
     
    If i > 0 Then 
         
        Do While szCut <> szPathSep 
             
            szCut = Mid$(FullPath, i, 1) 
             
            If szCut = szPathSep Then 
                 
                szPath = Left$(FullPath, i) 
                szFile = Right$(FullPath, Len(FullPath) - i) 
                 
            End If 
             
            i = i - 1 
        Loop 
         
        Select Case ReturnType 
        Case GetPath 
            StripFileOrPath = szPath 
        Case GetFile 
            StripFileOrPath = szFile 
        End Select 
         
    Else 
         
        StripFileOrPath = CStr(Empty) 
         
    End If 
     
End Function 
 
 
Sub TestItForExcel() 
     '   Test for Excel
    MsgBox StripFileOrPath(ThisWorkbook.FullName, GetFile) 
    MsgBox StripFileOrPath(ThisWorkbook.FullName, GetPath) 
End Sub