Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Updating
Hi,
I have the following VB Coding which activates macros on a regula basis automatically, if Excel is left open. Sub RunOnTime() Application.OnTime Date + TimeSerial(15,0,0), "Refresh" End Sub Refresh is the macro which is being run. The RunOnTime macro, activates the Refresh macro at 3pm every day. Is there a way, or does anyone know, how I can get a macro similar t this to run on a weekly basis rather than a daily basis as this on currently does? Thanks In Advance Kiz -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Updating
Hi Kiza,
Application.OnTime Date + 7 + TimeSerial(15,0,0), "Refresh" should do the trick (assuming you want to refresh at 3:00pm in seven days time). Just FYI Date returns the number of days since 31 December 1899, so you can just do ordinary integer arithmetic with it. HTH Peter Beach "kiza " wrote in message ... Hi, I have the following VB Coding which activates macros on a regular basis automatically, if Excel is left open. Sub RunOnTime() Application.OnTime Date + TimeSerial(15,0,0), "Refresh" End Sub Refresh is the macro which is being run. The RunOnTime macro, activates the Refresh macro at 3pm every day. Is there a way, or does anyone know, how I can get a macro similar to this to run on a weekly basis rather than a daily basis as this one currently does? Thanks In Advance Kiza --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Updating
Thanks for that.
What you have shown me will allow the macro to be run in 7 days fro the date given. I would like the macro to run once a week until it i stopped automatically. This section of the vb coding: Application.OnTime Date + 7 + TimeSerial(15,0,0), "Refresh" : will only allow the macro to run again in 7 days time. I will the want it to run 7 days after that and then 7 days after that unti stopped. Is that possible -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Updating
Hi Kiza,
In the Refresh routine, simply call: Application.OnTime Date + 7 + TimeSerial(15,0,0), "Refresh" IOW the Refresh routine itself sets the next time it should be called. Regards, Peter Beach "kiza " wrote in message ... Thanks for that. What you have shown me will allow the macro to be run in 7 days from the date given. I would like the macro to run once a week until it is stopped automatically. This section of the vb coding: Application.OnTime Date + 7 + TimeSerial(15,0,0), "Refresh" : will only allow the macro to run again in 7 days time. I will then want it to run 7 days after that and then 7 days after that until stopped. Is that possible? --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Updating
Thanks...
I have the following coding: Dim dtScheduledTime As Date Sub Update() ActiveCell.Offset(1, 0).Activate Selection.Interior.ColorIndex = 3 dtScheduledTime = Now + TimeSerial(0, 0, 2) Application.OnTime dtScheduleTime, "Refresh" End Sub Sub StopRefresh() Application.OnTime dtScheduledTime, "Refresh", , True End Sub Sub Refresh() ActiveCell.Offset(1, 0).Activate Selection.Interior.ColorIndex = 3 End Sub Sub RunOnTime() Application.OnTime Date + TimeSerial(0, 0, 2), "Refresh" End Sub For some reason, Im not sure what it is, but I cannot get it to repea itself. Have I forgotten to do something -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Updating
Hi Kiza,
A really simple way to do this is create a new workbook with an autorun() macro that opens your workbook and runs the macro. Then create a task with windows schedule task feature to run the new workbook. -- John johnf 202 at hotmail dot com "kiza " wrote in message ... | Hi, | | I have the following VB Coding which activates macros on a regular | basis automatically, if Excel is left open. | | Sub RunOnTime() | Application.OnTime Date + TimeSerial(15,0,0), "Refresh" | End Sub | | Refresh is the macro which is being run. | | The RunOnTime macro, activates the Refresh macro at 3pm every day. | Is there a way, or does anyone know, how I can get a macro similar to | this to run on a weekly basis rather than a daily basis as this one | currently does? | | Thanks In Advance | Kiza | | | --- | Message posted from http://www.ExcelForum.com/ | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Updating
Hi Kiza,
Change Refresh to read: Sub Refresh() ActiveCell.Offset(1, 0).Activate Selection.Interior.ColorIndex = 3 dtScheduledTime = Now + TimeSerial(0, 0, 2) Application.OnTime dtScheduleTime, "Refresh" End Sub HTH Peter Beach "kiza " wrote in message ... Thanks... I have the following coding: Dim dtScheduledTime As Date Sub Update() ActiveCell.Offset(1, 0).Activate Selection.Interior.ColorIndex = 3 dtScheduledTime = Now + TimeSerial(0, 0, 2) Application.OnTime dtScheduleTime, "Refresh" End Sub Sub StopRefresh() Application.OnTime dtScheduledTime, "Refresh", , True End Sub Sub Refresh() ActiveCell.Offset(1, 0).Activate Selection.Interior.ColorIndex = 3 End Sub Sub RunOnTime() Application.OnTime Date + TimeSerial(0, 0, 2), "Refresh" End Sub For some reason, Im not sure what it is, but I cannot get it to repeat itself. Have I forgotten to do something? --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic updating | Excel Worksheet Functions | |||
automatic updating in chart | Charts and Charting in Excel | |||
automatic updating | Charts and Charting in Excel | |||
automatic updating | Excel Discussion (Misc queries) | |||
Automatic updating | Excel Programming |