Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    one-time macro (Excel 2002)

    I have written a macro that fires when I create a new document from a template. It formats the sheets, names the file with a date-based name, saves the file to a specific folder based on the month and creates said folder it it does not already exist. Pretty cool, if I do say so myself.

    Problem: The macro fires each and every time the resulting file is re-opened, which really wrecks havoc. Is it possible to either kill the module after it runs once, or have is run only when the file is first created? Is it also possible to set the macro to run when a new file is created based on the template, but NOT RUN when the template itself is opened for editing?

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

    Re: one-time macro (Excel 2002)

    If you are using the Workbook_Open event, you could test for the path property, and if found exit sub.

    Try

    Private Sub Workbook_Open()
    If Len(Me.Path) Then Exit Sub
    ' put code for NEW workbook here
    End Sub


    Andrew C

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: one-time macro (Excel 2002)

    It sounds as if you're saving the workbook with the code in it. What you may need to do is to <UL><LI>Extract the formatted sheets from the template, using Sheets.Copy<LI>Then apply your SaveAs routine using ActiveWorkbook.SaveAs - leaving the code behind in the template<LI>Once you've finished with your new file, close it. The focus should shift back to your template.<LI>Exit the now unneeded template with<pre>With ActiveWorkbook
    .Saved = True
    .Close
    End With</pre>
    [/list]HTH
    Gre

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: one-time macro (Excel 2002)

    Andrew's and Hans techniques are nice, so here's my lazy one for contrast. In this case I force installation of the template to user's default template directories using a set name and making it read-only. Then each time the Workbook Open Event fires, it includes:

    If ThisWorkbook.Name Like "TemplateName*" Then
    ' do format, values and saving stuff (for example I stamp the user's name and suggest a file name)
    Else
    ' do other stuff

    Gotta make sure that the template name is unlikely to ever have much resemblance to the saved non-template name. Also, notice the asterisk at the end of "TemplateName*", because a read-only template called TemplateName is always initially renamed TemplateNamex where x is the number of times this template has been opened but not closed this session.
    -John ... I float in liquid gardens
    UTC -7DS

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: one-time macro (Excel 2002)

    <hr>Gotta make sure that the template name is unlikely to ever have much resemblance to the saved non-template name.<hr>

    That's why testing the Path property as Andrew did is a much better way.
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: one-time macro (Excel 2002)

    Andrew, on testing it appears that 'me' doesn't exist in an opened but unsaved template. However, Len(ThisWorkbook.Path) doesn't error out. Am I missing something?
    -John ... I float in liquid gardens
    UTC -7DS

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

    Re: one-time macro (Excel 2002)

    Me is only the same as "Thisworkbook" in the Thisworkbook code module.

    In general the keyword Me refers to the object the codepane belongs to. Since In Andrew's example it was in the thisworkbook module, it referred to that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: one-time macro (Excel 2002)

    Hi,

    A bit of addition to all the suggestions made so far...

    I have quite a few such templates which work fine with this bit of code
    in the Workbook_Open module (ThisWorkbook) of the template

    If ActiveWorkbook.Name Like "*.xlt" Then
    Exit Sub
    ElseIf Range("rg.Report_Processed").Value = True Then
    Exit Sub
    End If

    In this example,

    The first IF statement skips the rest of your Workbook_Open code if you've opened the original template.

    For the second requirement (don't go further if workbook is not a 'blank' template),
    there is a named range 'rg.Report_Processed' (could be placed on a hidden sheet).
    The value is kept FALSE in the original template.

    And it can made TRUE at run-time after saving the Template in a different name
    (maybe after the workbook is successfully saved)


    You can re-position these IF statements logically, wherever you need them in your Workbook_Open module.

    HTH <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Khushnood

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: one-time macro (Excel 2002)

    I think I get it. Me.Path errors in the Intermediates window or in a module, but works in ThisWorkbook Object code. Do I get it?
    -John ... I float in liquid gardens
    UTC -7DS

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

    Re: one-time macro (Excel 2002)

    You get it ok.

    Me when used in a ThisworkBook code module refers to ThisWorkbook. When used in the code module of a Worksheet it refers to that worksheet. Similarly with a userform. Though a reserved word, it referes to nothing in a general module or the immediate window.

    For the sake of clarity, perhaps I should have used ThisWorkbook in place of Me, but I took the lazy way out.

    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
  •