Is there an event that you can trap after a workbook has been saved?

My issue is this: I have some objects in a collection indexed by workbook
name, thus each object in the collection is associated with a particular
workbook. When the user changes the name of the workbook (by Save As...)
the keys need to be updated. I have written the code to do the updating,
including getting the previous name (via the BeforeSave event) but no event
to trigger it on.

Stewart

Roedd <<S. I. Becker wedi ysgrifennu:

Is there an event that you can trap after a workbook has been saved?

My issue is this: I have some objects in a collection indexed by
workbook name, thus each object in the collection is associated with
a particular workbook. When the user changes the name of the
workbook (by Save As...) the keys need to be updated. I have written
the code to do the updating, including getting the previous name (via
the BeforeSave event) but no event to trigger it on.


In the ThisWorkbook module:

Option Explicit

Private WithEvents m_objApp As Application

Private Sub m_objApp_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now, "'AfterSave'"
End Sub

Private Sub Workbook_Open()
Set m_objApp = Application
End Sub

In a regular module:

Sub AfterSave()
MsgBox "Saved"
End Sub

Problem is, I don't see a way of getting the name of the saved file. I
suppose you could loop through the workbooks collection looking for names
that don't feature in your tracking collection, but there's got to be a more
elegant way, surely?

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

Thank you Rob - you just gave me the missing piece of the puzzle.

The old name of the workbook can be saved it in a global variable in the
BeforeSave code. The new name is of course, the ThisWorkbook.Name property
by the time the AfterSave code is running.
You also need to do a check to make sure the user isn't trying to close the
workbook, but because of the order that events are fired on a save-and-close
this is not difficult.

There is one issue: if the user presses "Cancel" on the Save As dialog box,
then the AfterSave code will still run, even though the file hasn't been
saved, but in this case that doesn't matter.

The following seems to work (adapted from Rob's code):

Put this into the ThisWorkbook module:

Option Explicit

' No need for a WithEvents Object as Excel does all that for us in the
ThisWorkbook code module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
BeforeSave SaveAsUI, Cancel
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
BeforeClose Cancel
End Sub

And put this into a regular module:

Option Explicit
Option Private Module

Private OldName as String
Private SavedAsUI as Boolean
Private Closing as Boolean

Public Sub BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
OldName = ThisWorkbook.Name
SavedAsUI = SaveAsUI
' Save in Global variables anything else you would like to pass to
"AfterSave" subroutine...
' Do other BeforeSave code
If Not (Cancel Or Closing) Then Application.OnTime Now, "AfterSave"
End Sub

Public Sub BeforeClose(Cancel As Boolean)
' Do other BeforeClose code
Closing = Not Cancel
End Sub

Private Sub AfterSave()
' WARNING! This code will still run even if the user presses "Cancel" on
the SaveAs dialog box
' Although most of the time this will not really be a problem
Dim NewName as String
NewName = ThisWorkbook.Name

If SavedAsUI And NewName < OldName Then
' Do Name change ....
End If
' Do other AfterSave code
End Sub

Roedd <<S. I. Becker wedi ysgrifennu:

Thank you Rob - you just gave me the missing piece of the puzzle.

The old name of the workbook can be saved it in a global variable in
the BeforeSave code. The new name is of course, the
ThisWorkbook.Name property by the time the AfterSave code is running.
You also need to do a check to make sure the user isn't trying to
close the workbook, but because of the order that events are fired on
a save-and-close this is not difficult.


I didn't realise you were tracking each workbook within itself (I thought
you were using a single workbook to hook events for /all/ open workbooks).

In that case, you can do something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.OnTime Now, "'AfterSave """ & ThisWorkbook.Name & """'"
End Sub

Sub AfterSave(OldName As String)
MsgBox OldName & " Saved As " & ThisWorkbook.Name
End Sub

You can pass other params too (but only simple data types).

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

I didn't realise you were tracking each workbook within itself (I thought
you were using a single workbook to hook events for /all/ open workbooks).


Actually using one workbook to keep track of all the others is what I'm
doing, but the key that I needed was to put Application.OnTime Now,
FunctionName into the BeforeSave Event handler. Thanks for your help.

Stewart

Hi Stewart

Here's a trick from Tom Ogilvy:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim F As Variant
If SaveAsUI = True Then
Cancel = True
F = Application.GetSaveAsFilename(Me.Name, _
"Workbook (*.xls), *.xls")
If F = False Then Exit Sub
Me.SaveAs CStr(F)
MsgBox Me.FullName, , "After save !!!"
End If
End Sub

HTH. Best wishes Harald

"S. I. Becker" skrev i melding
...
Is there an event that you can trap after a workbook has been saved?

My issue is this: I have some objects in a collection indexed by workbook
name, thus each object in the collection is associated with a particular
workbook. When the user changes the name of the workbook (by Save As...)
the keys need to be updated. I have written the code to do the updating,
including getting the previous name (via the BeforeSave event) but no
event to trigger it on.

Stewart