The function below can be used to return the file name or the folder name from a full file name:

Function FileOrFolderName(InputString As String, _
    ReturnFileName As Boolean) As String
' returns the foldername without the last pathseparator or the filename
Dim i As Integer, FolderName As String, FileName As String
    i = 0
    While InStr(i + 1, InputString, Application.PathSeparator) > 0
        i = InStr(i + 1, InputString, Application.PathSeparator)
    Wend
    If i = 0 Then
        FolderName = CurDir
    Else
        FolderName = Left(InputString, i - 1)
    End If
    FileName = Right(InputString, Len(InputString) - i)
    If ReturnFileName Then
        FileOrFolderName = FileName
    Else
        FileOrFolderName = FolderName
    End If
End Function

Sub TestFileOrFolderName()
    MsgBox FileOrFolderName(ThisWorkbook.FullName, False), , _
        "This Workbook Foldername:"
    MsgBox FileOrFolderName(ThisWorkbook.FullName, True), , _
        "This Workbook Filename:"
End Sub