Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Save File to specific location when closing
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. -- Thanks, Angie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Save File to specific location when closing
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 he 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.) paankadu wrote: 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. -- Thanks, Angie -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Save File to specific location when closing
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 On Mon, 26 Oct 2009 14:41:02 -0700, paankadu wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Save File to specific location when closing
Forgot that one<g
Gord On Mon, 26 Oct 2009 17:13:45 -0500, Dave Peterson wrote: But what happens if the user wants to close the workbook without saving and then your macro saves the file automatically. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto save Spread sheet as htm to a specific location | Excel Discussion (Misc queries) | |||
Macro - save to current location vs excel default location | Excel Discussion (Misc queries) | |||
How to skip the save file notoification on file closing? | Excel Worksheet Functions | |||
Using Macro to Save Copy of File to New Location | Excel Discussion (Misc queries) | |||
Save to specific location | Excel Discussion (Misc queries) |