How to detect a template (2000 SP3)

Topic

New Reply

I have written a macro to allow a user to enter a customised footer, this is stored in ThisWorkbook in a template and is set to run on opening the template. I want the macro to become inactive after the workbook has been saved as an .Xls file.

I have tried the code:

If Application.ActiveWorkbook.FileFormat xlTemplate Then
GoTo lastline
End If

to skip the footer writing code and go to the end of the macro, however it seems that once the template has been opened it ceases to have fileformat xlTemplate, so this approach is not working. Can anyone help?

Reply | Quote

Replies

Would it be acceptable to test for the workbook being saved?

If UCase(Right(ActiveWorkbook.Name, 4)) = ".XLS" Then

When a new workbook is being created, its name doesn’t end in .xls yet, but once it has been saved, it does.

Reply | Quote

Works perfectly

Many thanks Hans

Reply | Quote

A workbook that has been created from a template has no path, so:

If Me.Path=”” Then
‘Fresh from template
Else
‘Has been saved sometime, could be the template itself
End If

Reply | Quote

If you do not employ any other code in the ThisWorkbook object, you could use a self destruct Workbook Open event as follows :

Private Sub Workbook_Open()
    If Me.FileFormat  xlTemplate Then
        '
        ' Insert  footer writing code here
        '
        With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
            .DeleteLines 1, .CountOfLines
        End With
    End If
End Sub

If the workbook is opened as a template all the action code is skipped, otherwise your Footer Writing code is run and then the whole subprocedure deletes itself, as it is no longer needed.

Andrew C

Reply | Quote

Thank you Jan and Andrew

Reply | Quote