How to get the file extension of the open workbook

How to determine if the open workbook is a template (.xltm) or not. Basically, I have a template. If the user opens the template as (right-click >open) as .xltm file and tries to run a macro, I should prevent a macro from being executed. If the user double-clicks the template, it opens as .xlsm, in that case I have no issue.

Can someone please help me figure this out? Thanks in advance.

Regards,

4 Answers

you can use below example to get extension of file

Sub extd() Dim extFind As String Dim sFile As String Const FilePath As String = "C:\Users\aa\Desktop\devces.docx" sFile = Dir(FilePath & filename & "*") extFind = Right$(sFile, Len(sFile) - InStrRev(sFile, ".")) MsgBox extFind End Sub 

I was looking for the same. Since ActiveWorkbook.Name depends on Windows property Hide extensions for known file types (If u have them hidden .Name wont return the extension), u can use Workbook.FileFormat. Returns an integer value, based on XlFileFormat enumeration. So, to check:

Option Explicit Sub sample() Debug.Print ActiveWorkbook.FileFormat Select Case ActiveWorkbook.FileFormat Case xlOpenXMLWorkbookMacroEnabled '52 xlsm Debug.Print "Its a workbook with macros enabled" Case xlOpenXMLTemplateMacroEnabled '53 xltm Debug.Print "Its a template with macros enabled" Case xlWorkbookDefault '51 xlsx Debug.Print "Its a workbook without macros" End Select End Sub 

Debug.Print Outputs to inmediate window, u can open it with Ctrl+G or in the view menu of the VB Editor.

Use the Code below:

'e.g. Active Workbook name = text.xlsx Dim wk AS Workbook: Set wk = ActiveWorkbook Dim fileExtension As String fileExtension = Right(wk.FullName, Len(wk.FullName) - InStrRev(wk.FullName, ".")) 'File Extension is now "xlsx" (without the .) 

fileExtension will now contain the workbook type, which can be used as you desire.

I like to use the FileSystemObject

Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.GetExtensionName(ActiveWorkbook.FullName) = "xltm" Then