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

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

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.

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.

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?

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?