VBA Get File Extension – From Path – using Excel macro

Extract File Extension from Path

A file extension is the text part of a file name after the last Dot ‘.’ . This extension name is usually used by the operating system to find out which application should process the file.

For example: If the file name has .Docx as the extension, then OS will know that it is a work document & open the file using the MS word in windows system.

Similarly, we use applications of these files extension very often:

• .xlsx – Excel File
• .txt – Text file
• .xml – Xml markup file
• .pdf – Adobe PDF document
• .py – file with Python code
• .vcf – Contacts data file
• .csv – comma separated file
• .exe – Executable file
• .bas – VBA Module extension
• .html – Web page code
• .vcf – vCard contacts file
• .apk – Android app install file
• .png, .jpeg, .bmp – Image file extensions
• .mov, mpeg – Video files

and it goes on. Two methods are explained in this page to get these file extensions.

1. VBA Code to Get File Extension (using FileSystemObject)

In case You want to get only the file extension name from the file name,  this code can be used.

It does not matter whether the file name has the path or only the filename.

This code works in both cases & returns only the file extension.

Function getFileExtension(sFilePath As String) As String
    Dim oFSO As Object

    'Init File system object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    'Return File Extension
    getFileExtension = oFSO.GetExtensionName(sFilePath)
End Function

This function returns the file extenstion in the same case as how it is mentioned in the input. Here are few sample results.

Also note in the above example, the input can be just a file name or full file path.

2. Find File Extension (using String operation)

Though it uses only string operations, this code also returns the same result.

Function get_file_extension(sFileName As String) As String
    'Variable declarations - Code by Officetricks.com
    Dim sFileExtension As String
    Dim iLastDot As Double
    
    'Find the position of Dot '.' before extension name
    iLastDot = VBA.InStrRev(sFileName, ".")
    
    'Now Get only the file entension
    sFileExtension = VBA.Right(sFileName, VBA.Len(sFileName) - iLastDot)
    
    'Return File Extension
    get_file_extension = sFileExtension
End Function

The code actually find the position of last dot.

Then it strips the text after the last dot. Usually that is where the file extensions comes up. It then returns the file extension to the calling program.

External Reference:

1. Different type of File formats & their extensions – read here.
2. This reference topic also explains the topic discusssed in this page.