macro wich saves files

[orquidea]

Hi All:

I would like to set a macro which automatically saves every 10 minutes a
file which is open 24/7.

Could anyone help me with it.

Thanks in advance for your help.

Orquidea

 

[JLatham]

The code below should do the trick for you. Put it in the Workbook's code
segment. To get there, with Excel 2003 and earlier:
open the workbook
right-click on the Excel icon immediately to the left of the word File in
the main menu and choose [View Code] from the list that appears.
Copy the code below and paste it into the module.
Save and close the workbook.

From then on the code will begin running when you open the file. Given that
it runs without other interruption for some weird reason, it should save
itself every 10 minutes, give or take one or two seconds as long as it
remains open. The one exception would be around midnight, in which case it
could be up to 20 minutes between the last save in the previous day and the
first one for the current day.

Private Sub Workbook_Open()
Dim lastSavedClick As Long
Const delayInSeconds = 600 ' 10 minutes * 60 seconds

'get current system timer
lastSavedClick = Timer ' timer is system clock, so to speak
'set up a loop that won't end
'since 1 never equals zero
Do Until 1 = 0
'allow other things to happen, such
'such as using this workbook, or others
'or using other programs
DoEvents
If Timer > (lastSavedClick + delayInSeconds) Then
'time to save the file
ThisWorkbook.Save
'reset our timer
lastSavedClick = Timer
End If
'check and see if we rolled past midnight!
If Timer < lastSavedClick Then
'yes we just blew through midnight
lastSavedClick = Timer
End If
Loop
End Sub

 

[orquidea]

Thanks a lot, you ar a genious!

I have a very basic knowledge on macros. Would you mind helping me
understand the below:

1. Do I use DoEvents when I code functions in microsoft like Save, print,
etc?

Thanks
Orquidea

The code below should do the trick for you. Put it in the Workbook's code
segment. To get there, with Excel 2003 and earlier:
open the workbook
right-click on the Excel icon immediately to the left of the word File in
the main menu and choose [View Code] from the list that appears.
Copy the code below and paste it into the module.
Save and close the workbook.

From then on the code will begin running when you open the file. Given that
it runs without other interruption for some weird reason, it should save
itself every 10 minutes, give or take one or two seconds as long as it
remains open. The one exception would be around midnight, in which case it
could be up to 20 minutes between the last save in the previous day and the
first one for the current day.

Private Sub Workbook_Open()
Dim lastSavedClick As Long
Const delayInSeconds = 600 ' 10 minutes * 60 seconds

'get current system timer
lastSavedClick = Timer ' timer is system clock, so to speak
'set up a loop that won't end
'since 1 never equals zero
Do Until 1 = 0
'allow other things to happen, such
'such as using this workbook, or others
'or using other programs
DoEvents
If Timer > (lastSavedClick + delayInSeconds) Then
'time to save the file
ThisWorkbook.Save
'reset our timer
lastSavedClick = Timer
End If
'check and see if we rolled past midnight!
If Timer < lastSavedClick Then
'yes we just blew through midnight
lastSavedClick = Timer
End If
Loop
End Sub

Hi All:

I would like to set a macro which automatically saves every 10 minutes a
file which is open 24/7.

Could anyone help me with it.

Thanks in advance for your help.

Orquidea

 

[JLatham]

When to use DoEvents is often a matter of judgment. If you are inside of a
loop that could take a long time to execute, then you probably want to toss
one into the code just to keep the system from seeming to hang during that
loop's execution.

In the case of the loop we came up with earlier, because it is an infinite
loop, we definitely need one or nothing else would get done at all. There is
no way out of that loop, so the only way you will be able to work with other
sheets, or other applications is at those many, many times that the DoEvents
lets the system take care of other processes and interrupts.

Generally I wouldn't/don't put very many DoEvents in my code at all, even
when doing things like Print or Save - those are operations that are going to
happen, and the system is doing to deal with the multi-tasking part of those
processes. Consider this

ThisWorkbook.Save
DoEvents

By the time the DoEvents statement is evaluated, the .Save is pretty much
done - you can't really put the DoEvents inside of the .Save process.

Here is one 'trick' that you can use in your own code to help speed it up:

Application.ScreenUpdating = False

Put that at the start of processes that may be doing a lot of work that
involves changing what the user might see on the screen - filling lists of
cells on worksheets, jumping around between several worksheets (or back and
forth between just 2 many times). By not having to refresh the video
display, processing time can be reduced by as much as a factor of 10 or more!
Technically the equivalent of an
Application.ScreenUpdating = False
statement is executed automatically by the system when you get to the End
Sub statement, so you don't have to worry about that. You'll see in code
that I write that I usually put that statement in it - I like to make sure I
"clean up after myself" in my code, even if someone assures me that it isn't
really necessary. Old school I guess, from my "for every PUSH let there be a
POP" coding of real time air traffic control systems software.

Thanks a lot, you ar a genious!

I have a very basic knowledge on macros. Would you mind helping me
understand the below:

1. Do I use DoEvents when I code functions in microsoft like Save, print,
etc?

Thanks
Orquidea

The code below should do the trick for you. Put it in the Workbook's code
segment. To get there, with Excel 2003 and earlier:
open the workbook
right-click on the Excel icon immediately to the left of the word File in
the main menu and choose [View Code] from the list that appears.
Copy the code below and paste it into the module.
Save and close the workbook.

From then on the code will begin running when you open the file. Given that
it runs without other interruption for some weird reason, it should save
itself every 10 minutes, give or take one or two seconds as long as it
remains open. The one exception would be around midnight, in which case it
could be up to 20 minutes between the last save in the previous day and the
first one for the current day.

Private Sub Workbook_Open()
Dim lastSavedClick As Long
Const delayInSeconds = 600 ' 10 minutes * 60 seconds

'get current system timer
lastSavedClick = Timer ' timer is system clock, so to speak
'set up a loop that won't end
'since 1 never equals zero
Do Until 1 = 0
'allow other things to happen, such
'such as using this workbook, or others
'or using other programs
DoEvents
If Timer > (lastSavedClick + delayInSeconds) Then
'time to save the file
ThisWorkbook.Save
'reset our timer
lastSavedClick = Timer
End If
'check and see if we rolled past midnight!
If Timer < lastSavedClick Then
'yes we just blew through midnight
lastSavedClick = Timer
End If
Loop
End Sub

Hi All:

I would like to set a macro which automatically saves every 10 minutes a
file which is open 24/7.

Could anyone help me with it.

Thanks in advance for your help.

Orquidea

 

[orquidea]

Thanks a lot for your clear explanation. The code and the explanations you
have put on every statement plus this explanation have help me a lot.

Have a good Weekend.

When to use DoEvents is often a matter of judgment. If you are inside of a
loop that could take a long time to execute, then you probably want to toss
one into the code just to keep the system from seeming to hang during that
loop's execution.

In the case of the loop we came up with earlier, because it is an infinite
loop, we definitely need one or nothing else would get done at all. There is
no way out of that loop, so the only way you will be able to work with other
sheets, or other applications is at those many, many times that the DoEvents
lets the system take care of other processes and interrupts.

Generally I wouldn't/don't put very many DoEvents in my code at all, even
when doing things like Print or Save - those are operations that are going to
happen, and the system is doing to deal with the multi-tasking part of those
processes. Consider this

ThisWorkbook.Save
DoEvents

By the time the DoEvents statement is evaluated, the .Save is pretty much
done - you can't really put the DoEvents inside of the .Save process.

Here is one 'trick' that you can use in your own code to help speed it up:

Application.ScreenUpdating = False

Put that at the start of processes that may be doing a lot of work that
involves changing what the user might see on the screen - filling lists of
cells on worksheets, jumping around between several worksheets (or back and
forth between just 2 many times). By not having to refresh the video
display, processing time can be reduced by as much as a factor of 10 or more!
Technically the equivalent of an
Application.ScreenUpdating = False
statement is executed automatically by the system when you get to the End
Sub statement, so you don't have to worry about that. You'll see in code
that I write that I usually put that statement in it - I like to make sure I
"clean up after myself" in my code, even if someone assures me that it isn't
really necessary. Old school I guess, from my "for every PUSH let there be a
POP" coding of real time air traffic control systems software.

Thanks a lot, you ar a genious!

I have a very basic knowledge on macros. Would you mind helping me
understand the below:

1. Do I use DoEvents when I code functions in microsoft like Save, print,
etc?

Thanks
Orquidea

The code below should do the trick for you. Put it in the Workbook's code
segment. To get there, with Excel 2003 and earlier:
open the workbook
right-click on the Excel icon immediately to the left of the word File in
the main menu and choose [View Code] from the list that appears.
Copy the code below and paste it into the module.
Save and close the workbook.

From then on the code will begin running when you open the file. Given that
it runs without other interruption for some weird reason, it should save
itself every 10 minutes, give or take one or two seconds as long as it
remains open. The one exception would be around midnight, in which case it
could be up to 20 minutes between the last save in the previous day and the
first one for the current day.

Private Sub Workbook_Open()
Dim lastSavedClick As Long
Const delayInSeconds = 600 ' 10 minutes * 60 seconds

'get current system timer
lastSavedClick = Timer ' timer is system clock, so to speak
'set up a loop that won't end
'since 1 never equals zero
Do Until 1 = 0
'allow other things to happen, such
'such as using this workbook, or others
'or using other programs
DoEvents
If Timer > (lastSavedClick + delayInSeconds) Then
'time to save the file
ThisWorkbook.Save
'reset our timer
lastSavedClick = Timer
End If
'check and see if we rolled past midnight!
If Timer < lastSavedClick Then
'yes we just blew through midnight
lastSavedClick = Timer
End If
Loop
End Sub


:

Hi All:

I would like to set a macro which automatically saves every 10 minutes a
file which is open 24/7.

Could anyone help me with it.

Thanks in advance for your help.

Orquidea

 

[JLatham]

You are very welcome. Thank you for your feedback, it is good to know that I
helped you achieve a solution and managed to teach a little in the process.

Thanks a lot for your clear explanation. The code and the explanations you
have put on every statement plus this explanation have help me a lot.

Have a good Weekend.

When to use DoEvents is often a matter of judgment. If you are inside of a
loop that could take a long time to execute, then you probably want to toss
one into the code just to keep the system from seeming to hang during that
loop's execution.

In the case of the loop we came up with earlier, because it is an infinite
loop, we definitely need one or nothing else would get done at all. There is
no way out of that loop, so the only way you will be able to work with other
sheets, or other applications is at those many, many times that the DoEvents
lets the system take care of other processes and interrupts.

Generally I wouldn't/don't put very many DoEvents in my code at all, even
when doing things like Print or Save - those are operations that are going to
happen, and the system is doing to deal with the multi-tasking part of those
processes. Consider this

ThisWorkbook.Save
DoEvents

By the time the DoEvents statement is evaluated, the .Save is pretty much
done - you can't really put the DoEvents inside of the .Save process.

Here is one 'trick' that you can use in your own code to help speed it up:

Application.ScreenUpdating = False

Put that at the start of processes that may be doing a lot of work that
involves changing what the user might see on the screen - filling lists of
cells on worksheets, jumping around between several worksheets (or back and
forth between just 2 many times). By not having to refresh the video
display, processing time can be reduced by as much as a factor of 10 or more!
Technically the equivalent of an
Application.ScreenUpdating = False
statement is executed automatically by the system when you get to the End
Sub statement, so you don't have to worry about that. You'll see in code
that I write that I usually put that statement in it - I like to make sure I
"clean up after myself" in my code, even if someone assures me that it isn't
really necessary. Old school I guess, from my "for every PUSH let there be a
POP" coding of real time air traffic control systems software.

Thanks a lot, you ar a genious!

I have a very basic knowledge on macros. Would you mind helping me
understand the below:

1. Do I use DoEvents when I code functions in microsoft like Save, print,
etc?

Thanks
Orquidea

:

The code below should do the trick for you. Put it in the Workbook's code
segment. To get there, with Excel 2003 and earlier:
open the workbook
right-click on the Excel icon immediately to the left of the word File in
the main menu and choose [View Code] from the list that appears.
Copy the code below and paste it into the module.
Save and close the workbook.

From then on the code will begin running when you open the file. Given that
it runs without other interruption for some weird reason, it should save
itself every 10 minutes, give or take one or two seconds as long as it
remains open. The one exception would be around midnight, in which case it
could be up to 20 minutes between the last save in the previous day and the
first one for the current day.

Private Sub Workbook_Open()
Dim lastSavedClick As Long
Const delayInSeconds = 600 ' 10 minutes * 60 seconds

'get current system timer
lastSavedClick = Timer ' timer is system clock, so to speak
'set up a loop that won't end
'since 1 never equals zero
Do Until 1 = 0
'allow other things to happen, such
'such as using this workbook, or others
'or using other programs
DoEvents
If Timer > (lastSavedClick + delayInSeconds) Then
'time to save the file
ThisWorkbook.Save
'reset our timer
lastSavedClick = Timer
End If
'check and see if we rolled past midnight!
If Timer < lastSavedClick Then
'yes we just blew through midnight
lastSavedClick = Timer
End If
Loop
End Sub


:

Hi All:

I would like to set a macro which automatically saves every 10 minutes a
file which is open 24/7.

Could anyone help me with it.

Thanks in advance for your help.

Orquidea