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.
- Different type of File formats & their extensions – read here.
- This reference topic also explains the topic discusssed in this page.
Additional Topics that might Interest you.