Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro wich saves files
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro wich saves files
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro wich saves files
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 "JLatham" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro wich saves files
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. "orquidea" wrote: 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 "JLatham" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro wich saves files
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. "JLatham" wrote: 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. "orquidea" wrote: 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 "JLatham" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro wich saves files
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. "orquidea" wrote: 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. "JLatham" wrote: 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. "orquidea" wrote: 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 "JLatham" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
wich formula | Excel Worksheet Functions | |||
I need a macro that saves a spreadsheet on two drive at once, | Excel Worksheet Functions | |||
Macro that saves excel workbook as PDF?? | Excel Discussion (Misc queries) | |||
How do i create a macro that saves the filename that is equal to a cell in the sheet? | Excel Discussion (Misc queries) | |||
Why does Excel saves all my files as temporary files? | Excel Discussion (Misc queries) |