Timer Event

[Saxman]

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

 

[Chip Pearson]

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]

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, procedure:=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.

 

[Chip Pearson]

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]

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?

 

[Chip Pearson]

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]

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, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Range("A1:A5").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
StartTimer

End Sub

 

[Chip Pearson]

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]

If I understand you correctly, then yes you are correct.


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.