Excel "AfterSave"

[ErikJan]

I want to clear calculated data before saving a workbook. After the save is done, I'd like to re-generate the data (it's very quick, but a lot of cells). Now the first part is easy via the BeforeSave event but how do I trigger the data recreation? Set a flag in the beforesave and pick that up later to recreate?

[HansV]

Perhaps you can use Application.OnTime to schedule a macro to be run (for example) 10 seconds after saving the workbook:

Code: Select all

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' code to clear calculated data
    ' ...
    ' schedule RegenerateData
    Application.OnTime Now + TimeSerial(0, 0, 10), "RegenerateData"
End Sub

In a standard module:

Code: Select all

Sub RegenerateData()
    ' your code here
    ' ...
End Sub

[ErikJan]

Makes sense... Two questions:
1. What would happen if the save is still ongoing? E.g. as there's is a slow network connection, or if a "Save As" was done and a dialog-box is still open?
2. What would happen if I close the WB (when it was changed) and say yes to the save prompt. The file can be saved and unloaded before the timed code is called. Will that trigger an error or will it simply not happen?

[HansV]

1. You'd have a problem. I don't think there's a way to test whether the save has finished.
2. The macro will still be scheduled even if the workbook has been closed. So Excel will reopen the workbook and run the macro.

(In short: I don't think there is a good way to do what you want)

[Jan Karel Pieterse]

If you unschedule the ontime event in the Workbook_Deactivate event you can ensure the ontime event does not run when the workbook is closed after the save.
AFAIK the ontime event is triggered after the save has finished.

[rory]

Or upgrade to 2010 or later where there is an AfterSave event. ;)

[ErikJan]

I was looking for that but didn't see it... now I do! Looks like the answer.

Still, the other GURUs didn't come-up with this one, so I'm not ready to celebrate yet... :-) is there a caveat maybe?