VBA Express
Get File Extension [Archive] - VBA Express Forum


View Full Version : Get File Extension

09-25-2007, 06:55 AM
Hi, I would like code for a macro function that does the following:

Looks in a specified file, (S:\filepath\filename), without knowing the extension of the file, and displays the extension for the first file found with a matching file name.

For instance:
In cell A1 the user would type: '600001' (file name) and in cell A2 there would be the function '=ExtFind(A1)' and it would display '.xls'. The file path will always be the same: S:\Data\ManEng\BOS\MFGENG\
If there is no file found, it would be nice if A2 displayed "No File"

Can anyone help me? I have been trying to figure this out for a long time. :mkay

09-25-2007, 06:56 AM
I am using Excel 2003.

09-25-2007, 07:08 AM
Function ExtFind(filename)
Const FilePath As String = "S:\Data\ManEng\BOS\MFGENG\"
Dim sFile As String

sFile = Dir(FilePath & filename & "*")
If sFile = "" Then
ExtFind = "No file"
ExtFind = Right$(sFile, Len(sFile) - InStrRev(sFile, "."))
End If
End Function

09-25-2007, 07:44 AM
Thank you SOO much. Thats perfect.

09-25-2007, 09:00 AM
You can also use Split for getting parts of a file path. Very flexible, with a bit of practice.

ExtFind = Split(filename, ".")(UBound(Split(filename, ".")))