VBA – Extract the File Name from a Complete File Path and Name

Let’s briefly examine a few possible way we can extract a filename from a complete path\filename.extension string.

So say you have a string like:

C:\Documents and Settings\User\Desktop\Details.txt

and you want to extract just:

Details.txt

Table of Contents

 

VBA String Parsing

'---------------------------------------------------------------------------------------
' Procedure : GetFileName
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the filename from a path\filename input
'               Works with any values, even those for which the file doesn't actually
'               exist or you don't have access to it
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : string of a path and filename (ie: "c:\temp\test.xls")
'
' Usage:
' ~~~~~~
'? GetFileName("C:\Users\Daniel\Desktop\Capture.PNG")
'   Capture.PNG
'
'? GetFileName(currentdb.Name)
'   Files and Folders_V1.000.accdb
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-02-06              Initial Release
' 2         2024-05-24              Added ByVal
' 3         2024-10-12              Updated Error Handler
'                                   Updated Header
'---------------------------------------------------------------------------------------
Function GetFileName(ByVal sFile As String)
    On Error GoTo Error_Handler

    GetFileName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetFileName" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

Dir Function

Although the Dir() function can be very practical for extracting the filename from fully qualified filename/paths, it does have one limitation. That is that it only work if Dir() can actually find and access the file. So if you are building a string for something you’re going to create, or if the data was stored in a table, file , but you don’t currently have access to the drive, then Dir() will simply return an empty string “”.

This is why I typically privilege other more universal approaches.

'---------------------------------------------------------------------------------------
' Procedure : GetFilename_Dir
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the filename from a path\filename input
'               *** File must actually exist or else it return an empty string ""
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References:
'   https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function
'   https://support.microsoft.com/en-us/office/dir-function-1a1a4275-f92f-4ae4-8b87-41e4513bba2e
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : string of a path and filename (ie: "c:\temp\test.xls")
'
' Usage:
' ~~~~~~
'? GetFilename_Dir("C:\Users\Daniel\Desktop\Capture.PNG")
'   Capture.PNG
'
'? GetFilename_Dir(currentdb.Name)
'   Files and Folders_V1.000.accdb
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2008-02-06              Initial Release
' 2         2024-10-12              Updated Error Handler
'                                   Updated Header
'---------------------------------------------------------------------------------------
Public Function GetFilename_Dir(ByVal sFile As String) As String
    On Error GoTo Error_Handler

    GetFilename_Dir = Dir(sFile)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetFilename_Dir" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

File System Object

Just a quick note, if you’re thinking of using FSO in your database then seriously think about implementing Self Healing Object Variable to optimize performance.

'---------------------------------------------------------------------------------------
' Procedure : GetFilename_FSO
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the filename from a path\filename input
'               Works with any values, even those for which the file doesn't actually
'               exist or you don't have access to it
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
' References:
'   https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object
'   https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/getfilename-method-visual-basic-for-applications
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : string of a path and filename (ie: "c:\temp\test.xls")
'
' Usage:
' ~~~~~~
'? GetFilename_FSO("C:\Users\Daniel\Desktop\Capture.PNG")
'   Capture.PNG
'
'? GetFilename_FSO(currentdb.Name)
'   Files and Folders_V1.000.accdb
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2008-02-06              Initial Release
' 2         2024-10-12              Updated Error Handler
'                                   Updated Header
'---------------------------------------------------------------------------------------
Public Function GetFilename_FSO(ByVal sFile As String) As String
    On Error GoTo Error_Handler
    Dim fileName              As String
    Dim FSO                   As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")
    GetFilename_FSO = FSO.GetFileName(sFile)

Error_Handler_Exit:
    On Error Resume Next
    Set FSO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetFilename_FSO" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

Using APIs

#If VBA7 Then
    Private Declare PtrSafe Function PathStripPath Lib "shlwapi.dll" Alias "PathStripPathA" (ByVal pszPath As String) As Long
#Else
    Private Declare Function PathStripPath Lib "shlwapi.dll" Alias "PathStripPathA" (ByVal pszPath As String) As Long
#End If

'---------------------------------------------------------------------------------------
' Procedure : GetFilename_API
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the filename from a path\filename input
'               Works with any values, even those for which the file doesn't actually
'               exist or you don't have access to it
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' Dependencies: PathStripPath API Declaration
' References:
'   https://learn.microsoft.com/en-us/windows/win32/api/shlwapi/nf-shlwapi-pathstrippatha
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : string of a path and filename (ie: "c:\temp\test.xls")
'
' Usage:
' ~~~~~~
'? GetFilename_API("C:\Users\Daniel\Desktop\Capture.PNG")
'   Capture.PNG
'
'? GetFilename_API(currentdb.Name)
'   Files and Folders_V1.000.accdb
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2008-02-06              Initial Release
' 2         2024-10-12              Updated Error Handler
'                                   Updated Header
'---------------------------------------------------------------------------------------
Function GetFilename_API(ByVal sFile As String) As String
    On Error GoTo Error_Handler
    Dim sBuffer               As String

    sBuffer = sFile & String(260 - Len(sFile), vbNullChar)
    PathStripPath sBuffer
    GetFilename_API = Left$(sBuffer, InStr(sBuffer, vbNullChar) - 1)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetFilename_API" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

Page History

Date Summary of Changes
2011-06-08 Initial Release
2024-10-12 Complete re-write and added alternate approaches.

One response on “VBA – Extract the File Name from a Complete File Path and Name”