|
Subject:
|
Need help AFTER saving an Excel File
|
|
Posted By:
|
amerk20
|
Post Date:
|
1/10/2006 1:14:27 PM
|
In Excel 2003, I currently have code that changes the xlCalculation setting to MANUAL (instead of Automatic) at the BEFORESAVE Event of "this workbook". What I'm now looking for is a way to "programmatically" turn the xlCalculation back on once the save is complete.
I haven't been able to find any "AFTER SAVE" or "POST SAVE" events. Does anyone have any ideas? When a user saves the Excel file, I want the calculations "turned off" and when the save is complete, turned back on.
Any help you can provide would be greatly appreciated.
Rick
|
|
Reply By:
|
maccas
|
Reply Date:
|
1/10/2006 1:22:32 PM
|
Rick,
You could cancel the original user prompted save event and do your own as follows:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Cancel user prompted save procedure ...
Cancel = True
' ... and do our own
Application.Calculation = xlCalculationManual
ThisWorkbook.Save
Application.Calculation = xlCalculationAutomatic
End Sub
|
|
Reply By:
|
amerk20
|
Reply Date:
|
1/10/2006 2:32:22 PM
|
Maccas,
Thank you sooooo much. This worked perfectly!
Cheers!
Rick
|
|
Reply By:
|
NickB
|
Reply Date:
|
1/8/2007 12:25:12 PM
|
Hi,
I've come across a similar problem where I want to perform an operation "after save". I tried the solution you suggested maccas but when the execution reached the "ThisWorkbook.Save" line it invoked the Workbook_BeforeSave method again. Once this was complete it returned to the first invocation of Workbook_BeforeSave and continued through to the end. The problem with this was that the workbook wasn't saved. I am using Excel 2002 as opposed to 2003 which Rick was using though - could this "problem/quirk" have been fixed in 2003?
I created a simple spreadsheet just to ensure that none of my other code was getting in the way and got the same result, this is the code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox ("before save")
ThisWorkbook.Save
MsgBox ("after save")
End Sub
With this code I saw the "before save" message twice followed by the "after save" message twice.
Thanks,
Nick
|
|
Reply By:
|
maccas
|
Reply Date:
|
1/8/2007 12:28:59 PM
|
Try setting Application.EnableEvents = False as the first line of the sub (Application.EnableEvents = True back on at the end). This will stop Excel registering events whilst you're running your code.
Maccas
|
|
Reply By:
|
NickB
|
Reply Date:
|
1/8/2007 12:42:15 PM
|
That did the trick nicely.
Thanks for your quick reply.
Nick
|
