CODEWITHSUNDEEP
excelvba
Terry
Terry

Reputation: 61

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,

Upvotes: 2

Views: 10067

Answers (4)

HackSlash
HackSlash

Reputation: 5803

I like to use the FileSystemObject

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

Upvotes: 1

Jeremy Morren
Jeremy Morren

Reputation: 764

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.

Upvotes: 1

Ameb
Ameb

Reputation: 93

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.

Upvotes: 2

manishsingh2061
manishsingh2061

Reputation: 521

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

Upvotes: 2

Related Questions