Excel VBA Get File Extension – 2 Simple Methods

How to Use Excel VBA to Get File Extension

Here is a simple code in VBA get file extension. 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. Excel VBA 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.

Based on the file extension type retuned, you can be sure that the right file is processed.

Here is our next method.

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.

VBA File Operations & File Properties

The file extensions are mostly 3 letters length except for few exceptions like html, xlsm, aspx etc.,We can use this method if you would like to add any suffix to the file name like a date or time stamp. After adding the suffix, you can add the file extension back to the file name.

It is very useful if you are creating multiple files with same name, but would like to have the version controlled.

Similar to this file extension, there are other functions available within Excel VBA & File system object. We can read file content or its properties like file size, creation date, last modified date, author, file storage path etc.,

Such function are much useful when we need to get data from external files and process them within Excel worksheet. Usually, we read data from csv files most of the times and sometimes from text, html files as well.

External Reference:

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

Additional Topics that might Interest you.

1. Excel VBA file operations
2. Excel VBA Get Username – 6 Easy Ways using Macro