Extract Extension name from File name
A file extension is the last 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 and open the file using the MS word in windows system.
Similarly, we use files with these 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
and it goes on.
How to get the extension from file name in VBA?
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 also or only the filename.
This code works in both cases & returns only the file extension
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.
