Auto save Excel document on certain day of the week

[UnitShift]

Hi there,

I am currently running Office 2011 on Mac OSX 10.6.8. I might just also add now that I am near clueless when it comes to Excel, I've never touched a macro and don't have the slightest clue where to even start looking for them...

Because we have to fill in timesheets at the place I work, I have over the months come up with an Excel document that just makes the process of recording my time much simpler. Using basic formulas I can record the start and finish of a project and it calculates total time and nicely updates the date, etc. Very basic functions thusfar, but useful. The file consists of a sheet for each day of the week, all saved in one workbook. I have managed to set it so that, if I update Monday's date, the date updates automatically throughout the sheets, giving me a start and end date, which I can use to save the file later. There are however one or two things I want to add to make it even simpler to use.

Currently, the thing I want to include most is the ability to automatically save a backup of the file on a weekly basis. I want it to save a backup document under a new name and in a different folder at the end of each Friday. Currently I just do this manually, but it would be very cool if, at the end of the day Friday, when/before I close the file for the last time that week, it saves the document as say organiser_20110712.xlsx (according to date of the Monday of that week) in my Backups folder, rather than the main folder I save the working file in.

Is there a way of doing this? If so, I would greatly appreciate some help. As I've mentioned, I'm very uninformed when it comes to Excel formulas and macro's.

 

[Smitty]

Welcome to the Board!

You could just go into Save As-->Tools-->General Options-->Always create backup.

But if you wanted to do it each Friday, I'd probably do something along these lines:

Use Workbook_Open even to evaluate the system day with WEEKDAY. If it =5 then save as, otherwise do nothing. You can get the save as part by recording a macro (which is important because it will capture your particular path). If you'll do that and post back someone can finish it for you.

HTH,

 

[UnitShift]

Hi Smitty, thanks for the response.

Use Workbook_Open even to evaluate the system day with WEEKDAY. If it =5 then save as, otherwise do nothing. You can get the save as part by recording a macro (which is important because it will capture your particular path). If you'll do that and post back someone can finish it for you.

Okay...where do I use Workbook_Open...? I'm VERY clueless with Excel, apologies if I sound daft...

 

[Smitty]

When you open the VBE (Visual Basic Editor), you'll see the module window on the right, which is where your code goes. On the left you'll see the Project Explorer (if you don't just hit CTRL+R). In there you'll see your Excel project, so expand the folder and look for the module named ThisWorkbook and open it (just double-click). Then in the module window on the right you'll see two drop-downs on the top; select the first one and choose "Workbook" (I'm not sure about a MAC, but it should be the only option). If the Open event doesn't populate for you, then select open from the drop-down on the right and it will.

Your backup code will go in there.

 

[UnitShift]

Okay cool, thanks, I've got that window open. Now I'm faced with this:

Private Sub Workbook_Open()

End Sub

Where do I type what now...?

 

[Smitty]

You can use something along these lines:

If Weekday(Date) = 5 Then Application.SaveAs "DrivePath:\Path\FIlename" & ".xls"

Just replace the demo code above with your recorded SaveAs code.

 

[UnitShift]

Okay cool, thanks Smitty. I've got this now:

Private Sub Workbook_Open()
If Weekday(Date) = 5 Then Application.SaveAs "Work:\Design Templates\Organiser" & ".xls"
End Sub

Will that work or will it make my machine self destruct...?

Also, how do I set the name of the file to save as the date of the Monday of that week...?

 

[Smitty]

If that's what you got when you recorded a save as, then it should be fine. The key is to use the recorded path/filename, because of accuracy.

To add the date you'd do something like this:

"...Filename - " & Format(Date,mm-dd-yy") - 4 & ".xls)

 

[UnitShift]

If that's what you got when you recorded a save as, then it should be fine. The key is to use the recorded path/filename, because of accuracy.

I entered that code manually in VBE, didn't know you had to record it...I've googled how to record a macro and followed the steps, but it does nothing to my code in VBE when I do a Save As while recording?

 

[Smitty]

You don't have to record it, but it's advisable because the recorder will capture the correct syntax for the file path/name/extension, etc., whereas if you do it by hand you're prone to errors. But the recorder isn't going to change any existing code, it will simply add to what's already been recorded by creating a new sub (if you're in a new instance of Excel it will create a new module as well).

But you can replace your hand-written file details and replace it with what the recorder gives you. That's not to say that you won't get it right if you type it by hand, it's just more reliable to let the machine do it.

Here's a primer on getting started with Macros.