Freezing/Logging Data from DDE at 1 Minute Intervals

[Ron1970]

I need to create a data logging program that can take up to 18 individual analog inputs into a paperless data recorder and store the values as read at the concurrent times as defined by me (One minute intervals).

I am currently attempting to create a very simple (in theory) spreadsheet.
I am getting data streamed to my PC using a DDE file
I need to capture individual cells value at a start time and save that value into another sheet to populate.

Once the previous value has been saved into the new row, a minute will go by and a new row will show the next value and so on.

Example of one value:
Oxygen is reading 12.0% over an hour period with variations during that time of 4 points (11.8-12.2)
the readings change continuously
at 1:00 pm I wish to record what the value of that cell is reading in the DDE, then every minute, read and log that new value in a cell below the last.

Start and stop would be initiated with a button control

example :
Current Value: 11/15/2024 1:06 P.M. 12.0 <<From DDE Cell

New Spreadsheet

11/15/2024 1:00 P.M. 11.9
11/15/2024 1:01 P.M. 12.1
11/15/2024 1:02 P.M. 11.9
11/15/2024 1:03 P.M. 12.2

 

[Anthony47]

You need to activate every minute a macro that copy the data from the dde sheet to a log sheet.
This require an "OnTime" scheduling.

For example, open a new workbook and assign to its sheet the name "LogSheet"; then (a) open the vba, (b)create a new Standard Vba Module and copy in it the following code:

Dim NextRun As Date, ddWs As Worksheet, LWs As Worksheet

Sub StartOnTime()
On Error GoTo NotStarted
'
Set ddWs = Workbooks("TheDdeFile.xlsx").Sheets("TheSheetName")      '<<< Read from here
Set LWs = ThisWorkbook.Sheets("LogSheet")                           '<<< The log worksheet
'
NextRun = Now + TimeSerial(0, 1, 0)                 'Set the first schedule time
Application.OnTime NextRun, "LogData"               'Schedule Sub LogData
'
Debug.Print ">>>> Started", Now, NextRun
Exit Sub
NotStarted:
Debug.Print ">>>> ddWs Is " & TypeName(ddWs) & ", LWs Is " & TypeName(LWs), Now
MsgBox ("Cannot schedule, ddWs is " & TypeName(ddWs) & ", LWs is " & TypeName(LWs))
Set ddWs = Nothing
Set LWs = Nothing
End Sub



Sub LogData()
Dim NextR As Long
'
Debug.Print ".."
On Error GoTo GErr
If Not (ddWs Is Nothing) And Not (LWs Is Nothing) Then
'collect data:                                                  '<<< Adapt the data to be copied
    Debug.Print "Collecting data at " & Format(Now, "hh:mm:ss"), NextRun
    NextR = LWs.Cells(Rows.Count, "A").End(xlUp).Row + 1
    LWs.Cells(NextR, 1).Value = Now                             'Time in column A
    LWs.Cells(NextR, 2).Value = ddWs.Range("B2").Value          '<<< First value (from B2 in this example) to column B
    '<<< etc etc
    '<<< etc etc
'
    'Schedule next running:
    NextRun = Now + TimeSerial(0, 1, 0)                         'Set next schedule time
    Application.OnTime NextRun, "LogData"                       'Re-schedule Sub LogData
    Debug.Print "Restarted", Now, NextRun
End If
Exit Sub
'
GErr:
'In case of error:
Debug.Print "---- Stopped at " & Now
MsgBox ("Stopped")
End Sub

This code contains two macros:
1) Sub StartOnTime - You will execute once to start the 1 minute collection
2) Sub LogData - This is started after every 1 minute and copyes the data from the dde worksheet to the log sheet

There are several blocks and lines marked <<<: they have to be customized to with your real values (ie, the dde file name and worksheet name; which data need to be copied, from where to where)

When you are ready, execute Sub StartOnTime (from Excel, Alt-F8; select the macro; press Run).
At this point, every minute a new set of data will be copied to your log sheet

To stop the process: close the DDE workbook, wait one minute for the MsgBox that says "Stopped"; save and close the LogFile

Try...