I am trying to extract the file extension from an excel filepath, in order
to check for .xlsx .xlsm and .xls - for Excel 2003 and 2007.

What I would like to do is to have a message box pop up on the sheet
"FileExtension" saying something like:-

MsgBox "The file extension for this file is " & FileExt

This would preferably be in the Worksheet_Activate() procedure.

Thanks
Sandy

Try this

Dim fileExtension as String
Dim splitArray() As String
splitArray = Split(ThisWorkbook.name, ".")
fileExtension = splitArray(UBound(splitArray))

3rd line, Splits up a String, using the . character as where to split it so
Aworkbook.xls gets split into two.

4th line - assigns the variable fileExtension to the last element in the
array, which will be the extension.

Hope that makes sense.

2nd line - Split up a String,

"Sandy" wrote:

I am trying to extract the file extension from an excel filepath, in order
to check for .xlsx .xlsm and .xls - for Excel 2003 and 2007.

What I would like to do is to have a message box pop up on the sheet
"FileExtension" saying something like:-

MsgBox "The file extension for this file is " & FileExt

This would preferably be in the Worksheet_Activate() procedure.

Thanks
Sandy

Thank you very much - works perfectly
Sandy

"NateBuckley" wrote in message
...
Try this

Dim fileExtension as String
Dim splitArray() As String
splitArray = Split(ThisWorkbook.name, ".")
fileExtension = splitArray(UBound(splitArray))

3rd line, Splits up a String, using the . character as where to split it
so
Aworkbook.xls gets split into two.

4th line - assigns the variable fileExtension to the last element in the
array, which will be the extension.

Hope that makes sense.

2nd line - Split up a String,

"Sandy" wrote:

I am trying to extract the file extension from an excel filepath, in
order
to check for .xlsx .xlsm and .xls - for Excel 2003 and 2007.

What I would like to do is to have a message box pop up on the sheet
"FileExtension" saying something like:-

MsgBox "The file extension for this file is " & FileExt

This would preferably be in the Worksheet_Activate() procedure.

Thanks
Sandy