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
