Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Application Open/Save/Close? (XL97/WinNT4)

    I'm absolutely 100% certain this has been discussed before but I'm b********d if I can find it! So sorry, folks, for repeating a question, but ...

    How do you replicate Word's global Open/Save/Close subs - that are stored in Normal.dot and apply every time a document is opened/saved/closed - in XL?!

    Many thanks!
    Beryl M


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    You need to create class module and add it into an addin or personal.xls

    See this for MS knowledgebase.

    See this <post#=317877>post 317877</post#> for some example code for opening any workbook.

    Here is some info from Chip Pearson.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Thanks, Steve, that's great - the post unfortunately referred to XL2k, not 97 (so did the knowledgebase article, for that matter, but there was a link to the 97 version, so that's alright) but the rest was great.

    I've printed it all off and am about to get a headache studying it all!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Okay, I've gone through it all and I'm fairly certain I understand what's needed. In my personal.xls I've got:

    In an ordinary module:
    <pre>Option Explicit
    Dim myobject As New AutoSaveAndClose

    Private Sub Workbook_Open()
    Set myobject.appevent = Application
    End Sub</pre>


    In a class module called AutoSaveAndClose:
    <pre>Public WithEvents appevent As Application
    Dim oBook As Workbook, sName As String, sPath As String, cPath As String, ifExists As Boolean
    Dim nPath As String, nName As String, aPath As String, aName As String

    Private Sub appevent_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
    aPath = ActiveWorkbook.Path
    aName = ActiveWorkbook.Name
    cPath = "c:databacup"

    ifExists = ThisWorkbook.IsOnDisc(cPath, aName)

    If ifExists = True Then Kill (cPath + aName)
    Workbooks(aName).SaveAs (cPath + aName)

    ifExists = IsOnDisc(aPath, aName)

    If ifExists = True Then Kill (aPath + aName)
    Workbooks(aName).SaveAs (aPath + aName)
    End Sub</pre>


    It doesn't work. As in, the code is supposed to save a backup copy of the activeworkbook to the c:databacup directory before saving it again to it's proper place, and it isn't. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    What am I missing, please? <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


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

    Re: Application Open/Save/Close? (XL97/WinNT4)

    This routine

    Private Sub Workbook_Open()
    Set myobject.appevent = Application
    End Sub

    must be in the ThisWorkbook module, *not* in a standard module. The Open event is an event of the workbook, therefore it must be in the module that belongs to the workbook as a whole.

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

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Shouldn't you be using the BeforeSaveAs event in combination with the beforeclose event?

    It would be best to first test what event fires in which case (or maybe which events).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Yes, and I am going to be, but I've just set up the beforeclose one so far, and until that works I'm not setting the other one up! Needless to say, I have been testing it by closing, not saving.
    Beryl M


  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Yes, Hans, that's what I understood, but it also says that the dim myevents ... line should be in an ordinary module, and if it and the workbook_open sub are not in the same place the workbook_open just says the variable hasn't been whatchamacallited ...

    So if I put the workbook_open in ThisWorkbook and the dim statement in an ordinary module, how do I get them to talk to each other?!
    Beryl M


  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Ah-hah! Got it! I put the dim statement in ThisWorkbook, too - the thing was I forgot to close and reopen XL when I tried that before, and when I did that this time it worked!

    Thanks all each and every!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Sorry, Jan, it looks like you were right - there's definitely something needing sorting here!

    I've got the beforeclose working now, but as you probably noticed, it includes saving workbooks - so of course it fires the beforesave event. I had intended to just have the two events the same, more or less, except that one will close the workbook and the other won't, but of course this won't work.

    How would I phrase this code to stop the beforesave event firing when it's running the beforeclose at the time?!

    This is giving me a headache. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Beryl M


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

    Re: Application Open/Save/Close? (XL97/WinNT4)

    You could have left the declaration of myobject in a standard module, and declared it as public:

    Public myobject As New AutoSaveAndClose

    But putting the declaration in ThisWorkbook should work too.

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Ah - that's what I was doing wrong! I had it as a dim instead of a public! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    I don't suppose you can help with the other problem I'm now having, can you? See my post to JanKarel in this thread ...

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


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

    Re: Application Open/Save/Close? (XL97/WinNT4)

    You can declare a boolean variable CalledFromBeforeClose in the class module:

    Private CalledFromBeforeClose As Boolean

    and use it as follows:

    Private Sub appevent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    ' Signal that BeforeClose is fired
    CalledFromBeforeClose = True
    ' Other code to execute
    ...
    ' Reset CalledFromBeforeClose for the next time
    CalledFromBeforeClose = False
    End Sub

    Private Sub appevent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' Get out if BeforeClose was fired
    If CalledFromBeforeClose Then Exit Sub
    ' Code to execute
    ...
    End Sub

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Application Open/Save/Close? (XL97/WinNT4)

    Hans, you've done it again! You're a star! ... and I should've thought of that myself!

    Many thanks!
    Beryl M


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

    Re: Application Open/Save/Close? (XL97/WinNT4)

    This can be a devious thing to get to work in all situations. From the top of my head:

    - user just saves
    - user saves-as
    - user saves, but decides to cancel
    - user closes book, Excel asks for a save
    - user closes book, Excel asks for a save, user does so
    - user closes book, Excel asks for a save, user cancels save
    - user closes book, Excel asks for a save, user cancels save and close
    - user closes book, Excel doesn't ask for save (no changes)
    - user closes book, Excel doesn't ask for save (no changes), user cancels closure

    - same as all above, but invoked by macro

    One way to control whether an event does or doesn't do its work:

    At the top of the class module:

    Dim bDisableCloseEvent As Boolean
    Dim bDisableSaveEvent As Boolean

    Then in any other (event) module:

    Private Sub Workbook_BeforeClose(Args)
    bDisableSaveEvent=True
    'Code to save (or call to it)
    bDisableSaveEvent=False
    '....
    End Sub

    In the BeforeSaveAs sub:

    If bDisableSaveEvent Then Exit Sub

    Same goes for bDisableCloseEvent of course
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 2 12 LastLast

Posting Permissions

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