Macro to Save File to specific location when closing

[paankadu]

Is there a way to have a macro automatically save a file when closing it
down? Specifically looking for it to save in a certain location using the
date as part of the file name. It is a daily log and would like it to save
as "10.26.09daily_log" when the user updates the information and closes out
of it for the day. I have the form set up as a template currently.

Reason being is many of the users aren't computer savvy and to do this they
have to do file save as and then it gets saved in different folders,
different ways and it is a search mission to find all the locations. they
are comfortable opening the folder & entering the data but we would like them
to just be able to close out the worksheet and have it automatically save to
the current date and file name.

Thanks in advance for your assitance.

 

[Dave Peterson]

Maybe...

But what happens if the user wants to close the workbook without saving and then
your macro saves the file automatically.

If the user were closing without saving because of a major mistake (deleting too
much data/worksheets), then you may have bigger problems.

Instead I'd put a big old button in a prominent location (top row of the
worksheet) and have it run the macro.

Then you could control exactly what you want to happen. You will have to make
sure that the user learns to use your button, though.

You could stop the user from using the built-in save options by putting code
like this behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Cancel = True

MsgBox "Please use the big old button to save!" _
& vbLf & vbLf & "SAVE DID NOT WORK!"

End Sub

And then this would be the code that would be assigned to the Button from the
Forms toolbar (in a General module):


Option Explicit
Sub SaveMeNow()

Dim myPath As String
Dim myFileName As String

myPath = "C:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFileName = "DailyLog_" & Format(Now, "yyyymmdd-hhmmss") & ".xls"

Application.EnableEvents = False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=myPath & myFileName, _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

All this depends on the user allowing macros to run, too.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Gord Dibben]

Can be done.

Couple or three questions.

Would the workbook be closed more than once per day?

Would you want to overwrite the previously saved version with same date?

Would you want a warning that this was to be done or just do it with no
warning?

Or perhaps if closed more than once per day you would want sequentially
numbered saved files?

What if user simply wanted to save the workbook without closing?


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Forgot that one<g>


Gord