Checking file type

[I/ATech]

On a template that is used monthly, on Workbook_Open() a message alerts the user how to name the file and where to place it when saving it, then the macro deletes itself. Problem is, if the template is opened for modifications the macro runs and deletes itself. How would I check to see if the template itself has been opened to preempt executing the code? Or is there a better approach?

 

[ColinKJ]

I/ATech.

First, disable macros in excel. close excel.

Then open the Template, the macro can run as the macros are disabled.

Don't forget to re-enable macros afterwards.

Regards

ColinKJ

 

[I/ATech]

Thank you ColinKJ. By default we all have security set to medium, so we get the alert message that allows us to disable the macro prior to opening the template, or for that matter, the workbook. Problem is I do not control who will open the template as a template in order to make minor modifications, nor do I want to (some of the folks using these tamplates are in other parts of the country). So my thinking is to bypass the code if the template is opened, but not if a workbook is created from the template.

 

[GTO]

How about using Open with the Application object instead?

 

[lenze]

TOTALLY UNTESTED

Private Sub WorkBook_Open
If Right(Me.FullName,4)=".xlt" Then Exit Sub
'rest of code

lenze

 

[GTO]

Giving the template a name of: "XXXDeleteMe.xlt"

Create a new Class Module named: "Class1"

In the Class Module:

Option Explicit
 
Public WithEvents XLApp As Application
 
Private Sub XLApp_WorkbookOpen(ByVal Wb As Workbook)
    If Not InStr(1, Wb.Name, ".xlt", vbTextCompare) = 0 _
    And ThisWorkbook.Name = "XXXDeleteMe.xlt" Then
        MsgBox "Editing template"
    ElseIf InStr(1, Wb.Name, ".xlt", vbTextCompare) = 0 _
    And Not InStr(1, Wb.Name, "XXXDeleteMe", vbTextCompare) = 0 Then
        MsgBox "Delete me"
    End If
End Sub

In ThisWorkbook Module:

Option Explicit
 
Dim MyXL As New Class1
 
Private Sub Workbook_Open()
    Set MyXL.XLApp = Application
End Sub

Only cursorarily tested, but this should look to see if the template itself is being opened, or is another wb being created (based on the template).

Hope this helps,

Mark

 

[I/ATech]

GTO, I am not proficient enough to understand what you mean with your first reply, but will be doing some reading this weekend to try and understand the second.

Lenze, tried adding the code to the existing, but it did not prevent the deletion. On the other hand, while looking in VB Help and other places to understand it I picked up a few more pointers.

Great stuff, I am having a blast. Thanks!

 

[lenze]

Maybe try this as a test

Sub Check
MsgBox RIGHT(Me.FullName,4)
End Sub

to see what you get when you open as a xlt or xls

 

[I/ATech]

Lenze, I created a new Template, put the code in ThisWorkbook, and nothing happened whether I opened it as the template or a workbook from the template.