Ontime function

[muhoho]

I am trying to get data from a cell (changes in real time) and store the values every 5 mins in a row over a specified time during the day. I need code to reference the cell every 5mins and all these values to a row (say a time period of 5pm - 10pm) and do this over a weekly period.

Any help would be much appreciated

 

[xld]

Try this

Private Sub Workbook_Open()
Dim sh As Worksheet

    On Error Resume Next
    Set sh = Worksheets("Log")
    On Error GoTo 0
    If sh Is Nothing Then
    
        Worksheets.Add.Name = "Log"
        With Worksheets("Log")
        
            .Range("B1").Value = TimeSerial(5, 0, 0)
            .Range("C1").Value = TimeSerial(5, 5, 0)
            .Range("B1:C1").AutoFill .Range("B1:BJ1")
            .Range("B1:BJ1").NumberFormat = "hh:mm"
            .Range("A2").Value = "Value"
        End With
    End If
    
    If Date < DateSerial(2009, 3, 26) Then 'change to suit
    
        If Time < TimeSerial(5, 0, 0) Then
        
            Application.OnTime Date + TimeSerial(5, 0, 0), "AddDetails"
        Else
        
            Call AddDetails
        End If
    End If
End Sub

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

And in a standard code module add

Public nTime As Double

Sub AddDetails()
Dim cella As Range

    Set cell = Worksheets("Log").Range("A2").End(xlToRight)
    If cell.Column = Columns.Count Then Set cell = Worksheets("Log").Range("A2")
    cell.Offset(0, 1).Value = Worksheets("Data").Range("A5").Value
    nTime = Time + TimeSerial(0, 5, 0)
    If nTime <= TimeSerial(22, 0, 0) Then
        
        Application.OnTime Date + nTime, "AddDetails"
    End If
End Sub

 

[muhoho]

Hiya,

Many thanks for the code. I really appreciate it. I am assuming this is reading the data from cell A5? When I tried to run this it seems to be filling up the columns at fairly random intervals. Is there a way to get it to fill up in rows and start a new column each day? I also need to do be sure it enters the right value in the cell for the right time. Any ideas?

thanks again

 

[xld]

For me, this code is writing to a separate column every 5 minute interval, and it is writing the value from A5 on the Data worksheet.

 

[muhoho]

Hi,

Thanks again. I was wondering if there is a way of getting the code place it in rows with the time interval instead of columns but start a new column on a new day.