|
Nearly There?
With the grateful help of Chip Pearson, I have managed to assemble the
following code with a 'timer' event. Basically, I need to copy data from the "Show" worksheet 15 times with a 30 second delay and paste into the "Chartdata" worksheet. This it does successfully when I run the "The Sub" routine, but there is no 30 sec. delay in each copy/paste routine. Is this because every copy/paste operation requires sub routine name? I cannot use 'The Sub' name repeatedly, as VB sees it as a duplicate. I cannot get the 'Stop Timer' routine to work when I run it neither. Someting must be amiss. .................................................. ........................ Public RunWhen As Double Public Const cRunIntervalSeconds = 30 '30 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() StartTimer Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("C2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("D2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("E2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("F2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("G2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("H2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("I2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("J2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("K2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("L2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("M2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("N2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("O2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("P2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub .................................................. ......................... Sub StopTimer() On Error Resume Next Application.OnTime earliesttime:=RunWhen, _ procedu=cRunWhat, schedule:=False End Sub |
Nearly There?
If you execute StartTimer, then your code should run in 30 seconds and every
30 seconds thereafter. I don't see anything obviously wrong with it. If it is taking more than 30 seconds to do the copy, then that would be problematic. Also, OnTime is not preemptive. It will not interrupt Excel if it is busy. Try stripping out the copy and paste code and just have the bear bones code. Get all the procedures running. then start adding back in the code to do the copying. Just note, you can copy without all the selecting a screen activity. I can't tell what you are copying from worksheets Show as you never select anything, so it appears you are copying the same thing over and over - like I say, can't tell. Worksheets("Show").Range("A1:A10").Copy worksheets("ChartData").Range("B2").PasteSpecial xlValues If your intent is is to copy the same location in successive columns at 30 second intervals, then as written, you code won't do that. Remove all that code and try set rng = worksheets("ChartData").Range("IV2").End(xltoLeft) if rng.column < 2 then _ set rng = Worksheets("ChartData").Range("B2") Worksheets("Show").Range("A1:A10").Copy rng.pastespecial xlValues or post back with specifics. -- Regards, Tom Ogilvy "Saxman" wrote in message ... With the grateful help of Chip Pearson, I have managed to assemble the following code with a 'timer' event. Basically, I need to copy data from the "Show" worksheet 15 times with a 30 second delay and paste into the "Chartdata" worksheet. This it does successfully when I run the "The Sub" routine, but there is no 30 sec. delay in each copy/paste routine. Is this because every copy/paste operation requires sub routine name? I cannot use 'The Sub' name repeatedly, as VB sees it as a duplicate. I cannot get the 'Stop Timer' routine to work when I run it neither. Someting must be amiss. .................................................. ....................... Public RunWhen As Double Public Const cRunIntervalSeconds = 30 '30 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() StartTimer Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("C2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("D2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("E2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("F2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("G2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("H2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("I2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("J2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("K2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("L2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("M2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("N2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("O2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("P2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub .................................................. ........................ Sub StopTimer() On Error Resume Next Application.OnTime earliesttime:=RunWhen, _ procedu=cRunWhat, schedule:=False End Sub |
Nearly There?
On Wed, 14 Dec 2005 16:03:22 -0500, Tom Ogilvy wrote:
I can't tell what you are copying from worksheets Show as you never select anything, so it appears you are copying the same thing over and over - like I say, can't tell. It does copy the same range everytime, but the data is refreshed every 30 seconds from the web. I need to paste into successive columns so that the data can be read by a 3D graph and progressions monitored. If your intent is is to copy the same location in successive columns at 30 second intervals, then as written, you code won't do that. That was my intention. So far, I can download the data to ("Show") and get it refreshed at a chosen interval; also I have the graph cells formatted within ("Chartdata"), which is read by another worksheet ("Graph") (where the graph is placed). I need to link the worksheets ("Show")/("Chartdata") via copy/paste at a set interval by the user (which would normally be the same time interval for gathering data onto ("Show")). Remove all that code and try...... I will give it a go. Thanks! |
Nearly There?
Appears whether we start with The_Sub or StartTimer,
the moment The_Sub is run, there is no mechanism to pause please "Tom Ogilvy" wrote: If you execute StartTimer, then your code should run in 30 seconds and every 30 seconds thereafter. I don't see anything obviously wrong with it. If it is taking more than 30 seconds to do the copy, then that would be problematic. Also, OnTime is not preemptive. It will not interrupt Excel if it is busy. Try stripping out the copy and paste code and just have the bear bones code. Get all the procedures running. then start adding back in the code to do the copying. Just note, you can copy without all the selecting a screen activity. I can't tell what you are copying from worksheets Show as you never select anything, so it appears you are copying the same thing over and over - like I say, can't tell. Worksheets("Show").Range("A1:A10").Copy worksheets("ChartData").Range("B2").PasteSpecial xlValues If your intent is is to copy the same location in successive columns at 30 second intervals, then as written, you code won't do that. Remove all that code and try set rng = worksheets("ChartData").Range("IV2").End(xltoLeft) if rng.column < 2 then _ set rng = Worksheets("ChartData").Range("B2") Worksheets("Show").Range("A1:A10").Copy rng.pastespecial xlValues or post back with specifics. -- Regards, Tom Ogilvy "Saxman" wrote in message ... With the grateful help of Chip Pearson, I have managed to assemble the following code with a 'timer' event. Basically, I need to copy data from the "Show" worksheet 15 times with a 30 second delay and paste into the "Chartdata" worksheet. This it does successfully when I run the "The Sub" routine, but there is no 30 sec. delay in each copy/paste routine. Is this because every copy/paste operation requires sub routine name? I cannot use 'The Sub' name repeatedly, as VB sees it as a duplicate. I cannot get the 'Stop Timer' routine to work when I run it neither. Someting must be amiss. .................................................. ....................... Public RunWhen As Double Public Const cRunIntervalSeconds = 30 '30 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() StartTimer Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("C2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("D2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("E2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("F2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("G2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("H2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("I2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("J2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("K2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("L2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("M2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("N2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("O2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Show").Select Application.CutCopyMode = False Selection.Copy Sheets("Chartdata").Select Range("P2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub .................................................. ........................ Sub StopTimer() On Error Resume Next Application.OnTime earliesttime:=RunWhen, _ procedu=cRunWhat, schedule:=False End Sub |
Nearly There?
Try putting either of the following inbetween each copy and paste cycle
Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30 Application.Wait TimeSerial(0, 0, 30) You can get rid of the starttimer and stoptime subroutines. Don't think you need that. "Saxman" wrote: On Wed, 14 Dec 2005 16:03:22 -0500, Tom Ogilvy wrote: I can't tell what you are copying from worksheets Show as you never select anything, so it appears you are copying the same thing over and over - like I say, can't tell. It does copy the same range everytime, but the data is refreshed every 30 seconds from the web. I need to paste into successive columns so that the data can be read by a 3D graph and progressions monitored. If your intent is is to copy the same location in successive columns at 30 second intervals, then as written, you code won't do that. That was my intention. So far, I can download the data to ("Show") and get it refreshed at a chosen interval; also I have the graph cells formatted within ("Chartdata"), which is read by another worksheet ("Graph") (where the graph is placed). I need to link the worksheets ("Show")/("Chartdata") via copy/paste at a set interval by the user (which would normally be the same time interval for gathering data onto ("Show")). Remove all that code and try...... I will give it a go. Thanks! |
Nearly There?
On Wed, 14 Dec 2005 21:16:02 -0800, PY & Associates wrote:
Try putting either of the following inbetween each copy and paste cycle Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30 Application.Wait TimeSerial(0, 0, 30) You can get rid of the starttimer and stoptime subroutines. Don't think you need that. Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30 The above causes a syntax error and this- Application.Wait TimeSerial(0, 0, 30) .....pastes the first column and then hangs, even if I eliminate the starttimer and stoptime subroutines. There must be a simpler way of delaying an operation such as mine? Could a timer be placed in Excel on a hidden form? Something like this:- Private Sub Form-Load() Form1.Show Timer1.Interval = 30000 '30 seconds Timer1.Enabled = True End Sub I'm afraid that I am not very hot on VB, but I know the solution has got to be simple? That is why I haven't given up yet. |
Nearly There?
look at the help example for Wait:
If Application.Wait(Now + TimeValue("0:00:10")) Then MsgBox "Time expired" End If You don't have to use the if statement. Application.Wait(Now + TimeValue("0:00:30")) -- Regards, Tom Ogilvy "Saxman" wrote in message ... On Wed, 14 Dec 2005 21:16:02 -0800, PY & Associates wrote: Try putting either of the following inbetween each copy and paste cycle Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30 Application.Wait TimeSerial(0, 0, 30) You can get rid of the starttimer and stoptime subroutines. Don't think you need that. Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30 The above causes a syntax error and this- Application.Wait TimeSerial(0, 0, 30) ....pastes the first column and then hangs, even if I eliminate the starttimer and stoptime subroutines. There must be a simpler way of delaying an operation such as mine? Could a timer be placed in Excel on a hidden form? Something like this:- Private Sub Form-Load() Form1.Show Timer1.Interval = 30000 '30 seconds Timer1.Enabled = True End Sub I'm afraid that I am not very hot on VB, but I know the solution has got to be simple? That is why I haven't given up yet. |
Nearly There?
On Thu, 15 Dec 2005 15:42:15 -0500, Tom Ogilvy wrote:
look at the help example for Wait: If Application.Wait(Now + TimeValue("0:00:10")) Then MsgBox "Time expired" End If You don't have to use the if statement. Application.Wait(Now + TimeValue("0:00:30")) This works perfectly, but I now have to find a way of ending the loop, as it goes back to the 1st copy/paste routine. I have not included the MsgBox code yet, but that shouldn't make any difference? Thanks to you, I have overcome the hardest bit. |
Nearly There?
On Wed, 14 Dec 2005 21:16:02 -0800, PY & Associates wrote:
Try putting either of the following inbetween each copy and paste cycle Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30 Application.Wait TimeSerial(0, 0, 30) You can get rid of the starttimer and stoptime subroutines. Don't think you need that. This should read:- Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30) Application.Wait TimeSerial(0, 0, 30) Thanks for the mailing which I accidentally deleted. The above still copy/pastes the first column and then hangs. This works better as stated previously. Application.Wait(Now + TimeValue("0:00:30")) |
| All times are GMT +1. The time now is 10:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com