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" <john.h.williams@btinternet.com> wrote in message
news:17b4clf8iuzz4.1jcfynybdp88y.dlg@40tude.net...
> 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

[Saxman]

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, 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" <john.h.williams@btinternet.com> wrote in message
news:ka6w62nner3e.bzjtbcu2hbhe.dlg@40tude.net...
> 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, 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.

[Saxman]

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?

[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" <john.h.williams@btinternet.com> wrote in message
news:1wy28wzr7jb8f$.smat1ul8x6mh$.dlg@40tude.net...
> 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?

[Saxman]

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, 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" <john.h.williams@btinternet.com> wrote in message
news:oso1wbe2zguz.bqq9854w3oxn$.dlg@40tude.net...
> 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, procedure:=cRunWhat,
> _
> schedule:=True
> End Sub
>
> Sub The_Sub()
> Range("A1:A5").Select
> Selection.Copy
> Sheets("Sheet2").Select
> ActiveSheet.Paste
> StartTimer
>
> End Sub

[Saxman]

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.