Macro that saves workbook every 4 minutes

You could try using another sub that will set the workbook, and instead of you running SaveThis sub once, you run that other one which will be the starting kick. For example:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hope it helps.

This should solve all your issues:

Please Login or Register  to view this content.

This code MUST go inside ThisWorkbook corresponding code sheet in the VBA interface.

I hope that helps.

Remember that now you won't be running SaveThis, but GetSavesStarted instead (which will in turn run SaveThis).

Originally Posted by galvinpaddy

1) it also saves any other workbook that is open
...
The workbook's name changes daily, as we 'Save As' a new name that includes the date.

To avoid this, check whether ThisWorkbook.Name = the expected workbook name that includes the date. What is the file name of the new workbook that includes the date (give an example)? In the code below I've assumed the workbook file name is in the format "Workbook yyyy-mm-dd.xls", e.g. "Workbook 2012-05-03.xls" for today.

Originally Posted by galvinpaddy

2) if i have more than one workbook open, and close the one with this particular code, the code re-opens the sheet and saves it again and again, until i fully close excel.

To avoid this, cancel the OnTime procedure in the Workbook_BeforeClose event handler.

I've incorporated both these fixes in the code below:

Put the following code in the ThisWorkbook module

Please Login or Register  to view this content.

Put the following code in a standard module e.g. Module1

Please Login or Register  to view this content.

ThisWorkbook.Name does not include ".xls" in it...

Originally Posted by Pichingualas

ThisWorkbook.Name does not include ".xls" in it...

It does for me (Excel 2003), as demonstrated by this in the Immediate window:

?thisworkbook.Name
OnTime Save Workbook.xls

Anyway, if you are correct for the OP's Excel version I'm sure there's an easy way to modify my code to handle this.

Originally Posted by galvinpaddy

the work book name will be 4th May 2012 Production Schedule, with the only changes being the day and month.

But your code says:

Please Login or Register  to view this content.

which gives "Production Schedule 04-05-2012", therefore the code inside the If statement won't run if the workbook is named as you first said.

Try debugging with F8 to see whether the If statement is working as expected, or put MsgBox ">" & ThisWorkbook.Name & "<" before it. Or try the following If statement instead:

Please Login or Register  to view this content.

Make sure the workbook name in this If statement reflects exactly the actual workbook name, as I'm not sure what the name really is, given the 2 variants you've posted.

I don't have Excel 2010, but the code works perfectly in Excel 2003.

Originally Posted by galvinpaddy

Sheet name - 20-May-2012 Production Schedule

But that date is some days in the future. Also, 'sheet name'? The code is using workbook names.

Please Login or Register  to view this content.

And the line above is using the current date, which gives 04-May-2012 Production Schedule and therefore the names are still inconsistent. I suggest again using F8 and/or the MsgBox line to debug.

The code does not appear to run automatically. have i missed something?

The code runs automatically from the Workbook_Open handler.

Sry fella, i truly am an idiot. ok, date sorted.
Sheetname - was actually supposed to read Workbook name. (04-may-2012 Production Schedule)

Whilst pressing F8 i received an error msg - 'Cannot execute in break mode' which i can only assume meant it was working.

Something that confuses me though, i thought that when you saved something you could no longer click undo? as even when i manually save it, i can still undo data entry - how come?
Also, i have adjusted the timer to 15 seconds, but there just doesnt seem to be a save action happening?

Apologies for the dumbness and thanks for your patience fella!

Originally Posted by galvinpaddy

Workbook name. (04-may-2012 Production Schedule)

That name is still inconsistent with the code, but I'm sure you can spot the difference (look carefully).

Whilst pressing F8 i received an error msg - 'Cannot execute in break mode' which i can only assume meant it was working.

Put a breakpoint (F9) on the first line of SaveThis, then F5 (run) and then press F8 when the debugger breaks in the code.

For testing, you can run Workbook_Open from the debugger, you don't need to keep closing and reopening the workbook to make it run. But make sure the timer is not running - run StopTimer first.

I'm looking into it, give me a few minutes.

4 minutes to be exact

Put this in ThisWorkbook module:

Please Login or Register  to view this content.

Put this in a normal module, for example Module1:

Please Login or Register  to view this content.

That seems to be working fine here, it saved and all. Now I'm waiting another 4 minutes to see if it will have the error you mentioned about it reopening once it had been closed, but I'm confident it won't. I hope that helps

Always glad to help. Tell me if you have any other issues with this, cause I've known the Schedule:=False method does not always work right away.