After save event

  • Thread starter Thread starter Bart
  • Start date Start date
B

Bart

Hi,
I want to execute some code just after the user saved the
file. In excel97 I could not find something like an
AfterSave event (although a BeforeSave exsist).
Anybody any ideas?

Thanks
 
Hi,

You can use an OnTime event to fire a macro after the
save, but you do need to ensure the save has actually
taken place. E.g.:

In a normal module:

Sub AfterTheSave()
If Thisworkbook.Saved=True then
Msgbox "Saved, or at least not modified since last save"
Else
Msgbox "Not saved!"
End If
End Sub

In the Thisworkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
Application.OnTime Now, "AfterTheSave"
End Sub

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
AFAIK there is no easy way of doing this.

An off the top of my head solution would involve this.

In the BeforeSave event start a timer with an delay of around a couple of
seconds. The code for this will be whatever you want to execute upon saving
completion. In most cases I would imagine setting a delay of around a few
seconds would be sufficient although you would be the best judge. If Excel
is still busy saving the file the event will not be fired until it's
complete so this perhaps won't mater so much.

This is not very elegent and a bit fragile. So to enhance it you could
check the file date/time stamp before the save then in your code executed on
the timer you re-read this to make sure the file has been updated.

Whatever you implement I think it'll be a bit clumsy but it should be
possible to get the desired effect. Would like to hear of other suggestions
though.


--

Regards,


Bill Lunney
www.billlunney.com
 
Back
Top