Constant flow of data and need a timer

[Furrry]

Hi all

I am having real fun here,, I have a constant flow of data that is coming from a different program that inputs its results into a cell in excel.

What I am trying to do is set up excel to move down to the next cell at a set period, say every 15 mins, so that the data stored is in manageable chunks.
I would also like to run an auto save function at the same time to safeguard against system crashes. ( I dont want much do I.. )
I have tried every form of timer settings I can think of and loads off here and the net.
I'm not the quickest person with VBA but can usually get things to run after a while, but I have been on this for a few days now and its driving me nuts.

I think I'm right in saying that I will need to run the timer from a different spreadsheet as the flow of data wont allow me to run the macro at the same time (or vice verse).

Does this also mean I would be better trying to redirect the flow of data to a different sheet or workbook while the timer(s) do there job?


Can anyone help please?

Thanks in advance

 

[BiocideJ]

What you are probably needing is the Application.OnTime Method.
Without more specific detail it is hard to give you specifics, however, here is the MSDN help file on it to hopefully get you started.

Application.OnTime Method (Excel)

If you have more specific questions, post back.

As for the auto-save portions, something like this within the same subroutine as the OnTime should work...

ActiveWorkbook.Save

 

[Furrry]

Thanks for the quick reply BiocideJ,

I have been trying to use the OnTime function with the TimeSerial setting and am currently trying to wrap it in a "next" or "do" loop function with the timer set within it to delay it.

I will try to slot the save function into it when I get it up and running as I can see another problem arising with that due to the length of time it will take to save the amount of data and the fact that the data will still be coming in...lol


Thanks for your time

 

[BiocideJ]

Perhaps something along these lines in the This Workbook Object

Private Sub Workbook_Open()
    myLoop
End Sub




Sub myLoop()


'some other code


Application.Save
Application.OnTime Now + TimeValue("00:15:00"), "myLoop"    'schedules myLoop to run 15 minutes from now
End Sub

This will cause the loop to initialize when the workbook is opened and then it will just keep re-calling itself every 15 minutes.
As it stands right now, this macro will save the workbook in its current path and name every 15 minutes.

Also, you do NOT want to put an OnTime method in a loop as it will cause multiple instances to be scheduled concurrently which will result in it being triggered WAY more than it should be and actually will eventually run out of memory as each instance schedules more of itself exponentially.

 

[Furrry]

Also, you do NOT want to put an OnTime method in a loop as it will cause multiple instances to be scheduled concurrently which will result in it being triggered WAY more than it should be and actually will eventually run out of memory as each instance schedules more of itself exponentially.

Thanks for this piece of info, I'd never even considered an internal loop.. that could of been fun

This is the latest version of code I have got :-

Option Explicit

Dim OnTime As String
Dim tcount As Integer
Dim myInput As Integer








Public Sub Button1_Click()
'
'to start the timer




  Application.OnTime Now + TimeSerial(0, 0, 5), "bordercells"
  


End Sub


Public Sub bordercells()


    


    myInput = Sheets("Sheet1").Cells(3, 9).Value




    For tcount = 1 To myInput
        
[COLOR=#800080]        Application.Timer Now + TimeSerial(0, 0, 5)[/COLOR]
       
           With Sheets("Sheet1").Selection
                
                
                  Range(Selection.Offset(1, 0), Selection.Offset(1, 0)).Select
                End With
                
        "rest of code"


        End With
    
    Next tcount
'End With


End Sub

I have used a cell location for the number of "tcount" times and will calculate that figure properly later (curently set at 5) and have set the timer to 5 secs and the code itself is just set to border the requested cells while I'm testing it.

The purple code is giving me a "runtime 438" error (object doesnt support this property or method).

[

Code:
Private Sub Workbook_Open() myLoopEnd SubSub myLoop()'some other codeApplication.SaveApplication.OnTime Now + TimeValue("00:15:00"), "myLoop" 'schedules myLoop to run 15 minutes from nowEnd Sub</pre>
This will cause the loop to initialize when the workbook is opened and then it will just keep re-calling itself every 15 minutes.
As it stands right now, this macro will save the workbook in its current path and name every 15 minutes.

I will look into this again tomorrow, but I'm sure I tried it and it didn't work (except the "save" part), this is why I'm a bit like this



Thanks a lot for the time you have taken BiocideJ, much appreciated

 

[BiocideJ]

OK, based on what you have given, I have created this testing code for you to try out.
I made the code actually draw a border around the cells for the test so you can see exactly which cells are affected. (Easy enough to clear the borders after the test).

At any rate, let me know if you have any problems or have any feedback.

Option Explicit
Dim TIMER_SWITCH As Boolean 'allows you to turn OFF future OnTime events


Public Sub Button1_Click()
'
'to start the timer


TIMER_SWITCH = True 'turn Timer_Switch ON
bordercells


End Sub


Public Sub Button2_Click()
'
'to stop the timer
TIMER_SWITCH = False 'turn Timer_Switch OFF
End Sub


Public Sub bordercells()
Dim myInput As Long
Dim tcount As Long
myInput = Sheets("Sheet1").Cells(3, 9).Value


For tcount = 1 To myInput
    Selection.BorderAround 1, 1
    Selection.Offset(1, 0).Select
Next tcount


'Application.Save
If TIMER_SWITCH Then Application.OnTime Now + TimeSerial(0, 0, 2), "bordercells"


End Sub

Also, if you look at the code you will notice there are two button click codes. The first is to turn the timer on and start the indefinite looping. The second button has the sole purpose of turning the looping off if you want to exit the loop. Notice the last line of the bordercells code only runs the OnTime Method if TIMER_SWITCH = TRUE.

 

[Furrry]

Hi BiocideJ,

Sorry don't mean to be rude, I got put on another job..lol

I will put that into practice this weekend and let you asap.

Thanks very much for the time you have put in