Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to detect a template (2000 SP3)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to detect a template (2000 SP3)

    Would it be acceptable to test for the workbook being saved?
    <code>
    If UCase(Right(ActiveWorkbook.Name, 4)) = ".XLS" Then
    </code>
    When a new workbook is being created, its name doesn't end in .xls yet, but once it has been saved, it does.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to detect a template (2000 SP3)

    Works perfectly

    Many thanks Hans

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to detect a template (2000 SP3)

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to detect a template (2000 SP3)

    If you do not employ any other code in the ThisWorkbook object, you could use a self destruct Workbook Open event as follows :<pre>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</pre>

    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

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to detect a template (2000 SP3)

    Thank you Jan and Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •