Solved: Auto Backup - Save based on date

[debauch]

Hello VBA peers ...

Question,

I have this snippet of code which will autosave a backup to a desired location.
It will be a great help, to grab our files from another department. Rather than hunting them down, and asking them to save it on our drive every week, I want to set up an auto save, based on a day.

Sub backupBYDATE()
'auto save by date
Dim dname As String, strTest As String
dname = "l:\" & Format(Now(), "yyyy_mmdd")
strTest = Dir(dname, vbDirectory)
If (strTest = "") Then MkDir (dname)
ActiveWorkbook.SaveCopyAs dname & "\BK_" & ActiveWorkbook.Name
ActiveWorkbook.Save 'also save current file
End Sub

So, I had this code run on start-up, which would save it continuosly which is not really necessary. Is there any code out there I could use to have it run, on say, Tuesdays?

[mdmackillop]

You could put your code in its own workbook to run on opening and use Windows Task Scheduler to open that workbook when required.

[debauch]

Would the task scheduler need to be set up on the other workers stations?

Also, if they were currently using the file, would it save the current work before backing it up ( I guess I mean is there potential for it to crash if open when task is run). I ask this becasue they are a seperate department, but they constantly forget to save their files over for us. If I could do this, and not have them set up a task or anything, it would be great. I was gonig to borrow their files temp. and implment this.

I would rather implement a 'If' statement on startup if possible, to say :

if mm_dd = tuesday then
call backupbydate
end if


Also, the scheduler would not run if they system was logged off?

[mdmackillop]

Do you have access to their server? Ideally you want the code to run at night, when nothing is in use. Of course you could always use Windows Backup, which was designed for this.

[debauch]

I do have temp. access to their server to implement this code.

We have a drive, they have a drive. They are supposed to save their files to a third neutral drive. I would rather just go into their files, and set up the code to run on a specific day. That way, no matter what happens to their system (new software, system swap) they will alwasy use the same file, with the same macro.

[debauch]

I came accross this :


Private Sub Workbook_Open()
Application.OnTime TimeValue("15:00:00"), "MyMacro"
End Sub

Do you think this would work for days? Say, every 168:00:00??
I wonder if this would start time, from when file is open ?

[Simon Lloyd]

Debauch, the item you came across is part of the OnTime method the snippet you have would start "MyMacro" after 15 hours, so yes it could run your macro to save but doesnt solve your problem for opening and closing their workbooks, where to save them to and time or date stamping the back ups, also if you research this method properly you will see that the event would happen every 15 hours, to stop it you would need to pass the timer to a variable, i.e without stopping the timer the workbook would run the code every 15 hours if the workbook was closed it would open but not close after the timer had elapsed (i'm not quite sure on this last few words as im fairly new to all this stuff myself....but i'm sure one of the guru's here will put you right).

Regards,
Simon

[makako]

isnt is simpler to condition it to "=WEEKDAY(TODAY())" ?

[makako]

Sub backupBYDATE()
'auto save by date
if evaluate("=WEEKDAY(TODAY())" ) <> 3 then: exit sub
Dim dname As String, strTest As String
dname = "l:\" & Format(Now(), "yyyy_mmdd")
strTest = Dir(dname, vbDirectory)
If (strTest = "") Then MkDir (dname)
ActiveWorkbook.SaveCopyAs dname & "\BK_" & ActiveWorkbook.Name
ActiveWorkbook.Save 'also save current file
End Sub

[debauch]

Awsome!! ^^ Works great. Thanks you. Hit the nail on head.