Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+vba+training - On time
|
|
|
The UK's most regular 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+vba+training - On time
Resolved · Low Priority · Version Standard
Reply Fri 16 Nov 2007, 16:41Delegate Samantha said...
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.
For upcoming training course dates see: Pricing & availability
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
Reply Fri 23 Nov 2007, 15:12Delegate Samantha said...
RE: Run A Macro Every Hour
thanks for this carlos!
works brillantly!
however, i soimetimes get a run time error when closing down the workbook.
will investigate and if any probs will be in touch!
thanks for your help
sam
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
Reply Fri 23 Nov 2007, 15:13Delegate Samantha said...
RE: Run A Macro At a Predetermined Date And Time
again carlos, this worked excellently!
thanks again for the help!
cheers,
sam
|