MS Access – VBA – Determine a Filename without the Extension

Once again, in my programming career, I have on numerous occasions needed to extract the filename from a fully qualified ‘path/filename.extension’, but just the filename without the extension. Below, is a very simple function to do so.

'---------------------------------------------------------------------------------------
' Procedure : GetFileNameWOExt
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the filename without the extension from a path\filename input
' 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:
' ~~~~~~~~~~~~~~~~
' strFileWPath - string of a path and filename (ie: "c:\temp\test.xls")
'
' Usage:
' ~~~~~~~~
' ? GetFileNameWOExt("C:\temp\test.xls")
'       -> test
' ? GetFileNameWOExt("C:\Users\Dev\Desktop\My Database V1.000.accdb")
'       -> My Database V1.000
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-02-24             Initial Release
' 2         2020-04-24              Update to account for filenames with periods in them
'                                   Updated the function header
'---------------------------------------------------------------------------------------
Function GetFileNameWOExt(ByVal strFileWPath As String)
    On Error GoTo Error_Handler

    GetFileNameWOExt = Right(strFileWPath, Len(strFileWPath) - InStrRev(strFileWPath, "\"))
    GetFileNameWOExt = Left(GetFileNameWOExt, InStrRev(GetFileNameWOExt, ".") - 1)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetFileNameWOExt" & 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

3 responses on “MS Access – VBA – Determine a Filename without the Extension”

  1. Triacona

    amazing, thanks.
    I changed the InStr to InStrRev and this allows me to have the version no. in my application name within the file path, i.e. Planning Reports v4.4

  2. Higgsy

    What about a version that not only handles file names that include multiple periods, but can optionally return the file name including the extension:

    Function fnGetFileName(ByVal strFileWPath As String, Optional blnExcludeExt As Boolean) As String
    On Error GoTo ErrHandler
    strProcName = “fnGetFileName()”
    fnGetFileName = Right(strFileWPath, Len(strFileWPath) – InStrRev(strFileWPath, “\”))
    If Not blnExcludeExt Then
    GoTo ExitGetFileNameWOExt
    End If ‘Else strip off the extension.
    fnGetFileName = Left(fnGetFileName, InStrRev(fnGetFileName, “.”) – 1)

    ExitGetFileNameWOExt:
    Exit Function
    ErrHandler:
    etc…

    1. Daniel Pineault Post author

      Using any special characters (, . space etc.) is asking for trouble. I don’t allow it and most big companies put in place naming conventions to avoid such potential issues. I’d urge you to consider doing the same.