PDA

View Full Version : Excel Save Macro needed



Big John
14-09-2004, 10:43 PM
I need a macro that I can save a workbook with when it is closed by the user. I have added the following

Private Sub WorkBook_Close()
ActiveWorkbook.save
End Sub

This works but brings up a message that the user can cancel the save. I want to force the save of the data to the current name without the option of being able to cancel because I need to make this sheet idiot proof.

I thought there was a option to add to the save method but I cant find it back.

beama
14-09-2004, 10:57 PM
I did a google search on vba function save here (http://www.google.co.nz/search?hl=en&ie=UTF-8&q=vba+function+save&btnG=Google+Search&meta=)
and there seems to be two ways of achieving this The first two links off google
1 . ok=true
2. Application.DisplayAlerts = False
Wk.SaveAs Filename:="C:/MyData/SalesData.xls
the secound method is not as flexable as the first as the spreadsheet being saved has always got to have the SalesData.xls

HTH

Big John
14-09-2004, 11:32 PM
Thats basically what I have now but it will always ask if you want to overwrite the file and I want the answer to be yes all the time as it needs to be saved.
If the user can click no then the data can be lost. The sheet is a daily one which is saved using SaveCopyAs at the end of the run thus the original keeps its filename.
I have autosave on for every 15 minutes but this failed for some reason today.

parry
15-09-2004, 12:35 AM
Hi John, if your just saving the active workbook thats already been saved then you shouldnt be getting any alerts. Excel also has a BeforeSave event which may be what you want. Anyway, you can disable messages as mentioned above but you should always turn them back on. To resolve the problem of it not periodically saving try this...

Place this code in the ThisWorkbook module
Private Sub Workbook_Open()
'Set a timer to run the SaveMyWorkbook procedure in 15 mins
Application.OnTime Now + TimeValue("00:15:00"), "SaveMyBook"

End Sub

Place this code in a standard module (eg Module1)
Sub SaveMyBook()
'Turn off Excel message alerts
Application.DisplayAlerts = False

'Save this workbook.
ThisWorkbook.Save

'Turn back on alerts
Application.DisplayAlerts = True

'call this procedure again in another 15 mins
Application.OnTime Now + TimeValue("00:15:00"), "SaveMyBook"
End Sub

Russell D
15-09-2004, 09:53 AM
Something doesn't quite gel here.

You SaveAs at the ned of the run to retain the original file intact, but have AutoSave turned on during the run - this will be saving new data into the original file.
What appears should be happening is doing SaveAs as soon as the file is opened so the original file cannot be overwritten.
And, what are the rules regarding what the saveAs file name should be each day?
If it has say a sequential number or date appended to the original file name, this can be automated via code in a Auto_Open macro.

parry
15-09-2004, 10:03 AM
I agree Russell, that was my initial reaction too. It would be very easy to join say a name a date and a time in a string to be used as a filename for SaveAs or SaveCopyAs using the Workbook_Open event. Its hard to know whats going on though because maybe even a templates in order?

What confused me more was that when you save a document that has already been saved you will not get alerts. This should only occur in a SaveAs/SaveCopyAs type of scenario.

Big John
15-09-2004, 12:35 PM
Okay here is what I do.

The spreadsheet is used by operators to input plant data during a production run. Auto save is turned on so as to save every 15 minutes. If the auto save worked then there is no message from closing the file because the data has not changed.
At the end of the run I do not used SaveAs. I use SaveCopyAs which creates a full copy of the current sheet and leaves the original alone.

Now what happened yesterday is that for some reason the autosave failed to work and the data for the day was lost due to the operator closing the sheet down and must have answered no to saving the sheet that normally happens when you close a sheet that has data changed.

I need to make it idiot proof so that if anyone closes it then it gets saved regardless.

If Disabling the alerts means it will save then this is all I need to do so will give it a try. The documentation on it does not say what happens as the default response is when disabled. ie is it Yes or No.

parry
15-09-2004, 01:59 PM
> Okay here is what I do.
>
> The spreadsheet is used by operators to input plant
> data during a production run. Auto save is turned on
> so as to save every 15 minutes. If the auto save
> worked then there is no message from closing the file
> because the data has not changed.
> At the end of the run I do not used SaveAs. I use
> SaveCopyAs which creates a full copy of the current
> sheet and leaves the original alone.
>
> Now what happened yesterday is that for some reason
> the autosave failed to work and the data for the day
> was lost due to the operator closing the sheet down
> and must have answered no to saving the sheet that
> normally happens when you close a sheet that has data
> changed.
>
> I need to make it idiot proof so that if anyone
> closes it then it gets saved regardless.
>
> If Disabling the alerts means it will save then this
> is all I need to do so will give it a try. The
> documentation on it does not say what happens as the
> default response is when disabled. ie is it Yes or
> No.

Hi John, the line Application.DisplayAlerts = False (True = on, False = off) simply means turning messaging off - it doesnt save a workbook. For example when you close a workbook without saving you will get a message asking if you want to save - it just supresses the message. You will still need a line like ThisWorkbook.Save.

The issue is that the Save message comes up before the Workbook_Close event has had a chance to run so turning off alerts there wont achieve much. However, the Before_Close event preceeds the Close event & happens before the save message so this should do the business.

From VBA Help regarding the BeforeClose event...
"Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes."

Place the code in ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

Note that I have used ThisWorkbook.Save rather than ActiveWorkbook.Save. ActiveWorkbook means that whichever workbook has focus THAT workbook will be saved, so if you have >1 workbook open then the wrong book could be saved. ThisWorkbook.Save means it saves the workbook where the code itself is located. If however, you want to save just whatever book has focus then use ActiveWorkbook.Save.

hth

Russell D
15-09-2004, 05:51 PM
OK,

ActiveWorkbook.Close savechanges:=True


will close and save changes without alerts.

parry
15-09-2004, 06:54 PM
> OK,
>
> ActiveWorkbook.Close savechanges:=True
>
>
> will close and save changes without alerts.
>

Yup but it wont capture actions. You need an event. :-)

Big John
15-09-2004, 07:17 PM
> OK,
>
> ActiveWorkbook.Close savechanges:=True
>
>
> will close and save changes without alerts.
>

Thanks. Thats the one I could not remember.