» File names and folder names using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions 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