Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
At present I have an Excel spreadsheet that extracts data from the web.
This data is refreshed every 60 seconds. I would like to copy/paste this data about 10-15 times every 60 seconds to another worksheet or spreadsheet and convert the results to a graph. I would need to create a macro, but I am not sure how I could delay the copy/ paste routine for 60 seconds? Would I need a timer event or similar? Not being that adept to VB6, is there a useful bit of code that I can obtain and paste into the macro or will it require something like a hidden dialogue box with a timer on it? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
You can use the OnTime method of the Application object to run a
macro at a specified time. See www.cpearson.com/excel/ontime.htm for more details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Saxman" wrote in message ... At present I have an Excel spreadsheet that extracts data from the web. This data is refreshed every 60 seconds. I would like to copy/paste this data about 10-15 times every 60 seconds to another worksheet or spreadsheet and convert the results to a graph. I would need to create a macro, but I am not sure how I could delay the copy/ paste routine for 60 seconds? Would I need a timer event or similar? Not being that adept to VB6, is there a useful bit of code that I can obtain and paste into the macro or will it require something like a hidden dialogue box with a timer on it? TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
On Mon, 12 Dec 2005 07:47:05 -0600, Chip Pearson wrote:
You can use the OnTime method of the Application object to run a macro at a specified time. See www.cpearson.com/excel/ontime.htm for more details. I have tried running this simple bit of code below, but it doesn't like the Public (or Private) attribute. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 12/12/2005 by John ' ' Keyboard Shortcut: Ctrl+m ' Private RunWhen As Double Private Const cRunIntervalSeconds = 60 ' one minute Private Const cRunWhat = "The_Sub" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _ schedule:=True End Sub Sub The_Sub() Range("A1:A6").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste StartTimer End Sub .................................................. .... Would it be better use the Windows timer function with Office 2003? Do you have a working example that I could perhaps modify? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
What do you mean by "doesn't like"? Where are you placing the
code? In a standard code module? ThisWorkbook? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Saxman" wrote in message ... On Mon, 12 Dec 2005 07:47:05 -0600, Chip Pearson wrote: You can use the OnTime method of the Application object to run a macro at a specified time. See www.cpearson.com/excel/ontime.htm for more details. I have tried running this simple bit of code below, but it doesn't like the Public (or Private) attribute. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 12/12/2005 by John ' ' Keyboard Shortcut: Ctrl+m ' Private RunWhen As Double Private Const cRunIntervalSeconds = 60 ' one minute Private Const cRunWhat = "The_Sub" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _ schedule:=True End Sub Sub The_Sub() Range("A1:A6").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste StartTimer End Sub .................................................. ... Would it be better use the Windows timer function with Office 2003? Do you have a working example that I could perhaps modify? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
On Mon, 12 Dec 2005 08:29:56 -0600, Chip Pearson wrote:
What do you mean by "doesn't like"? Where are you placing the code? In a standard code module? ThisWorkbook? I get an error. The code below is in Modules/Module1. Should it be in 'This Workbook'? I have tried placing it there, but still get an error with the declarations. Sorry to be a pain, but I am all new to this, having only a basic knowledge of VB. This is something that I do not normally do, but thought it would be a challenge, hence wondering if there was a worked example somewhere on the web? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
All the code should be in Module1, or any regular code module,
as you already have it. What error do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Saxman" wrote in message .. . On Mon, 12 Dec 2005 08:29:56 -0600, Chip Pearson wrote: What do you mean by "doesn't like"? Where are you placing the code? In a standard code module? ThisWorkbook? I get an error. The code below is in Modules/Module1. Should it be in 'This Workbook'? I have tried placing it there, but still get an error with the declarations. Sorry to be a pain, but I am all new to this, having only a basic knowledge of VB. This is something that I do not normally do, but thought it would be a challenge, hence wondering if there was a worked example somewhere on the web? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
On Mon, 12 Dec 2005 09:40:02 -0600, Chip Pearson wrote:
All the code should be in Module1, or any regular code module, as you already have it. What error do you get? Thanks for your patience. I have got the following to run. I have also added a keyboard shortcut to 'StartTimer'. I altered the time to 5 seconds (so I wouldn't have to wait too long). If I wanted to copy/paste, say 10 times, then all I would need to do is to add the last routine 10 times and modifying the sheet numbers accordingly? I would need to add "sheet1" at the end of each routine to copy the new data. (I left it out for simplicity). How can I stop the macro once it has started, as it loops forever? Thanks again. It might be one small step for me, but one hell of a step to my progress! .................................................. ........................ Public RunWhen As Double Public Const cRunIntervalSeconds = 5 '5 seconds Public Const cRunWhat = "The_Sub" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _ schedule:=True End Sub Sub The_Sub() Range("A1:A5").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste StartTimer End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
If I wanted to copy/paste, say 10 times, then all I would need
to do is to add the last routine 10 times and modifying the sheet numbers accordingly? If I understand you correctly, then yes you are correct. How can I stop the macro once it has started, as it loops forever? You didn't read the whole page on my web site. There is an example procedure called StopTimer that will stop the timer process. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Saxman" wrote in message ... On Mon, 12 Dec 2005 09:40:02 -0600, Chip Pearson wrote: All the code should be in Module1, or any regular code module, as you already have it. What error do you get? Thanks for your patience. I have got the following to run. I have also added a keyboard shortcut to 'StartTimer'. I altered the time to 5 seconds (so I wouldn't have to wait too long). If I wanted to copy/paste, say 10 times, then all I would need to do is to add the last routine 10 times and modifying the sheet numbers accordingly? I would need to add "sheet1" at the end of each routine to copy the new data. (I left it out for simplicity). How can I stop the macro once it has started, as it loops forever? Thanks again. It might be one small step for me, but one hell of a step to my progress! .................................................. ....................... Public RunWhen As Double Public Const cRunIntervalSeconds = 5 '5 seconds Public Const cRunWhat = "The_Sub" Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _ schedule:=True End Sub Sub The_Sub() Range("A1:A5").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste StartTimer End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timer Event
On Mon, 12 Dec 2005 16:08:54 -0600, Chip Pearson wrote:
If I wanted to copy/paste, say 10 times, then all I would need to do is to add the last routine 10 times and modifying the sheet numbers accordingly? If I understand you correctly, then yes you are correct. How can I stop the macro once it has started, as it loops forever? You didn't read the whole page on my web site. There is an example procedure called StopTimer that will stop the timer process. Thanks for that. Will do tomorrow! Must get the VB books out! I have come across your site before using Google. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timer event | Excel Discussion (Misc queries) | |||
Macro With Timer Event | Excel Discussion (Misc queries) | |||
Timer Event | Excel Discussion (Misc queries) | |||
EXCEL 2002: How do I user/simulate a Timer event in Excel form | Excel Programming | |||
Timer Event | Excel Programming |