Get File Extension

[djmara]

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.
Thanks

[djmara]

I am using Excel 2003.

[Bob Phillips]

[vba]

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"
Else
ExtFind = Right$(sFile, Len(sFile) - InStrRev(sFile, "."))
End If
End Function
[/vba]

[djmara]

Thank you SOO much. Thats perfect.

[mdmackillop]

You can also use Split for getting parts of a file path. Very flexible, with a bit of practice.
[VBA]
ExtFind = Split(filename, ".")(UBound(Split(filename, ".")))

[/VBA]