98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » excel+vba+training - On time
excel+vba+training - On time
Resolved · Low Priority · Version Standard
Samantha has attended:
Excel VBA Intro Intermediate course
On time
i need to know how ot run a macro every hour.
also need to run a report on a particular date and time and then hold the macro as still running (but not stopping user from doing anything else), and then for it to run again a week later at the same date and time.
RE: on time
Hi Sam,
I know that one of our trainers, Carlos, has been working on a solution for this problem. I will check in with him today.
Regards, Rich
Run A Macro Every Hour
Hi Samantha
To Run A Macro every hour use the following code. Note that :
1. This code needs to be placed in the ThisWorkbook code area of the VBE. (In the Project window - Double click ThisWorkbook to open it)
2. Paste the code immediately below the Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This switches off the OnTime function otherwise the workbook would reopen every hour to run the macro
Application.OnTime TheTime, "MyMacro", , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:60:00), "MyMacro"
End Sub
In the Module your Macro is in you need to make the following changes:
Public TheTime As Date
'This is the variable that holds the time so the procedure can be cancelled on Close
Sub MyMacro()
TheTime = Now + TimeValue("00:60:00") 'Resets the timer to run in 60 mins
Application.OnTime TheTime, "MyMacro" 'Reruns the macro
'YOUR CODE
End Sub
Hope this helps
Carlos
Run A Macro At a Predetermined Date And Time
Hi Sam
To run a macro at a predetermined date and time:
On your spreadsheet use a cell eg "A1" to hold the date and time the system is to run the macro. eg. 30/11/07 15:00
Every time the macro runs this will be updated by advancing 7 days.
NB This needs to be physically in the Workbook. If held in a variable it could be be lost when the PC is switched off
This code needs to be placed in the ThisWorkbook code area of the VBE. (In the Project window - Double click This Workbook to open it)
Paste it immediately below the Option Explicit
Private Sub Workbook_Open()
'This sets the date the macro is to run into the system.
'If the date matches today's date the system will run te macro at the required time
RunMacroDate = Worksheets("Sheet1").Range("A1")
Application.OnTime RunMacroDate, "MyMacro"
End Sub
In the Module holding the Macro to be run, you need to make the following changes:
Public RunMacroDate As Variant
'Declared in the Option Explicit area
Sub MyMacro()
'YOURCODE
RunMacroDate = RunMacroDate + 7 'Adds 7 days to the RunMacroDate
Worksheets("Sheet1").Range("A1") = RunMacroDate 'Replaces the RunMacroDate on your worksheet with new value
End Sub
Regards
Carlos
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Navigate with keyboard without losing your active cellIf you like using your keyboard to scroll through your excel document, but want to keep your active cell the same, use the scroll lock, and then use your arrow keys to navigate around the document. |