PDA

View Full Version : Sleeper: Self saving workbook



russkie
08-31-2005, 02:23 PM
hey,

is there a way to make the active worksheets save itself every eh, few minutes?
it seems my cooworkers aren't aware of the "save" function and just close the program i made them without saving.

EDIT: well, i know excel saves itself but im pretty sure its to a tmep file and not the current one. the temp file i dont wanna have to check every morning to see if the temp file is different from the original.

thanks fer any help.

Jacob Hilderbrand
08-31-2005, 02:45 PM
If you don't want to use the auto recover option you can do something like this.

In a Standard Module:


Option Explicit

Sub SaveMe()
ThisWorkbook.Save
Application.OnTime Now + TimeSerial(0, 5, 0), "SaveMe"
End Sub


In the ThisWorkbook Module:


Option Explicit

Private Sub Workbook_Open()
Call SaveMe
End Sub

Zack Barresse
08-31-2005, 02:50 PM
Hi there,

check out this file here ... http://www.jkp-ads.com/Download.htm ... named Autosafe.xla.

Written by Jan Karel Pieterse, it's just what you need (I think).

russkie
09-01-2005, 08:44 AM
hey firefytr, actually DRJ got more what i need. The file you have linked is a bit more annoying becuase there is still the situation of having to load a different file if something happens to the main one, also it will delete the temp files when the main one is closed, whether or not when the main one is close it is saved.

But DRJ i got a question, your thing is perfect, but it doesnt start running when i load the program, only if i run the macro through VBA (i did that just to see if it works) and it does save every 5 min(i changed to 1 for fast testing :P).
now it could be one of 2 things (or something completely seperate...). see i put the macros into an empty excel workbook. the main workbook that the macros edit is a seperate file, i did this for saving purposes so any version of the main file saved will all point to one location of the macros. Soooooo... when i open the main spreadsheet, the spreasheet with the macros only opens up when you run a macro in the main. i ran one of them just the get macro sheet loaded up. so im thinking the "private sub workbook_open" will start running if you actually open up workbook manually and not through a macro... just an idea. the second option is...
i dunno what "option explicit" is... i mean i checked out the help files on it, i get it, but i dont understand why/how you put where it is... i put it before the creation of the sub, in the beginning of the module becuase if its not in the beginning itll cuase an error to the previuose sub since there is an action after the sub is "ended".

the saving works with the option explicit before the subs, or i just deleted those options explicits and it still works, but both situtaions only if i run the saveme/wokrbook_open macro manually. it will not start by itself... ideas?

TazGuy37
09-02-2005, 05:22 PM
But DRJ i got a question, your thing is perfect, but it doesnt start running when i load the program, only if i run the macro through VBA (i did that just to see if it works) and it does save every 5 min(i changed to 1 for fast testing :P).
...
i dunno what "option explicit" is... i mean i checked out the help files on it, i get it, but i dont understand why/how you put where it is... i put it before the creation of the sub, in the beginning of the module becuase if its not in the beginning itll cuase an error to the previuose sub since there is an action after the sub is "ended".


The code should start when the workbook is opened. Hence, you have to save it if not saved, and reopen it to get it to work.



Re: Option Explicit (from the help file):
When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time (http://javascript<b></b>:hhobj_8.Click()).


If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.

What that says is you have to declare all variables. And, if you don't give them a data type, they will be Variant (requiring at least 16 bytes to store).


If you check Tools, Options, Require Variable Declaration, Option Explicit will be inserted as the first line in all new modules you create.

Basically, Option Explicit (IMHO) is a good idea. If you spell a variable wrong (I do a lot) this will catch it.

Desert Piranha
09-02-2005, 06:07 PM
i dunno what "option explicit" is... i mean i checked out the help files on it, i get it, but i dont understand why/how you put where it is... i put it before the creation of the sub, in the beginning of the module becuase if its not in the beginning itll cuase an error to the previuose sub since there is an action after the sub is "ended".russkie,
Aditional info about "Option Explicit"
http://www.excelforum.com/showthread.php?t=384692