DDE Macros and data formatting

Hey,

I'm not totally new to Excel but I don't know a lot about using it so I hope this is the right forum to post in.

I'm working with a data logging system to collect data on a circuit. The logging software allows you to export the data collected to Excel in real time using DDE, and having talked to the logger company I managed to obtain a macro to get the data into excel (I'll put it at the end of the post). This works fine, and I can now press a button and Excel updates itself with the new data every second. What I want to do is quite complicated and I have no idea where to start:
1. The data obtained via the macro only displays in static cells, that is when the new data comes in it overwrites the old one. I want Excel to take a copy of each new bit of data as it comes in, and copy and paste it into a column on the same sheet so that it can be stored. I then want it to move down a row and take the next reading and so on, so that each time a new reading comes in, Excel takes the value and puts it in the next row down in a set column
2. Having a time elapsed measure would also be very useful, so that it records the time after starting the other macro in a column next to the ones collecting the data as described in 1. In this case, each new row would be 1 second further on. The actual time is not massively important, just the 'relative' time since the data collection started.
3. Once the data is copied into the columns to be stored (1.) I then want to calculate two sets of moving averages and place those in columns next to the data. My current plan is one average of 30 samples and another of 5.
4. Once I have the moving averages I can then get Excel to plot some graphs to show the progression of the two values
5. Since my project is revolving around detecting a short circuit in a coil of wire, it is looking for drops in resistance. I want Excel to be able to monitor the two calculated moving averages and if the difference between them exceeds a set threshold for more than 5 samples then some alarm is triggered (for now just a cell changing colour to red or a pop-up box or something).

Now this is quite a lot to be asking I know, but any help towards getting this working would be greatly appreciated!

The macro for obtaining the data via DDE is:

 '
' Macro recorded 24-11-00 by Martin Berriman
' Copyright Pico Technology Ltd 2000
'
'
Dim Repeat As Boolean

Sub PrepBook()
    Worksheets("Sheet1").OnSheetActivate = "Module1.AutoSheet"
End Sub

Sub AutoSheet()
    Repeat = False
End Sub

Sub GetDataButton() 'Get Data Button pressed
    If Repeat = False Then 'don't do anything if we're already getting data
        GetCurrentData
    End If
End Sub

Sub RepeatButton() 'Repeat Button pressed
    Repeat = True
    GetCurrentData
End Sub

Sub StopRepeatButton() 'Stop Repeat Button pressed
    Repeat = False
End Sub

Sub GetCurrentData()
    
    chan = DDEInitiate("PLW", "Current")
    If TypeName(chan) = "Error" Then
        Repeat = False
        MsgBox "PicoLog cannot be found - Macro Halted!!"
    Else
        returndata = DDERequest(chan, "Name")
        For i = LBound(returndata) To UBound(returndata)
            Worksheets("Sheet1").Cells(i + 3, 1).Value = returndata(LBound(returndata) + i - 1, 1)
        Next i
        returndata = DDERequest(chan, "Value")
        For i = LBound(returndata) To UBound(returndata)
            Worksheets("Sheet1").Cells(i + 3, 2).Value = returndata(LBound(returndata) + i - 1, 1)
        Next i
        returndata = DDERequest(chan, "Units")
        For i = LBound(returndata) To UBound(returndata)
            Worksheets("Sheet1").Cells(i + 3, 3).Value = returndata(LBound(returndata) + i - 1, 1)
        Next i
        DDETerminate chan
    End If
    If Repeat Then Application.OnTime Now + TimeValue("00:00:01"), "GetCurrentData"
End Sub

Don't promise a thing but you could try this. Try it first on a backup copy. Only changed the GetCurrentData routine.

Sub GetCurrentData()
    Dim lrow As Long
    lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
    lrow = lrow + 1
    chan = DDEInitiate("PLW", "Current")
    If TypeName(chan) = "Error" Then
        Repeat = False
        MsgBox "PicoLog cannot be found - Macro Halted!!"
    Else
        returndata = DDERequest(chan, "Name")
        For i = LBound(returndata) To UBound(returndata)
            Worksheets("Sheet1").Cells(lrow + i, 1).Value = returndata(LBound(returndata) + i - 1, 1)
        Next i
        returndata = DDERequest(chan, "Value")
        For i = LBound(returndata) To UBound(returndata)
            Worksheets("Sheet1").Cells(lrow + i, 2).Value = returndata(LBound(returndata) + i - 1, 1)
        Next i
        returndata = DDERequest(chan, "Units")
        For i = LBound(returndata) To UBound(returndata)
            Worksheets("Sheet1").Cells(lrow + i, 3).Value = returndata(LBound(returndata) + i - 1, 1)
        Next i
        DDETerminate chan
    End If
    If Repeat Then Application.OnTime Now + TimeValue("00:00:01"), "GetCurrentData"
End Sub

Thanks for your reply.

I tried that out, and it returns an error (1004) on the line

lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row

I tried changing it around a bit, changed the (1) to ("1"), but comes up with a different error (9, Subscript out of range).

I've zipped up a copy of the workbook so you can see how it's all set out in there, as I suspect that to make it work knowing where it's printing the data in the first place is necessary.

Originally Posted by JMann

Thanks for your reply.

I tried that out, and it returns an error (1004) on the line

lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row

I tried changing it around a bit, changed the (1) to ("1"), but comes up with a different error (9, Subscript out of range).

I've zipped up a copy of the workbook so you can see how it's all set out in there, as I suspect that to make it work knowing where it's printing the data in the first place is necessary.

Try this instead. On top of the GetCurrentData routine.

Sub GetCurrentData()
    Dim lrow As Long
    lrow = 3
    Do While Worksheets("Sheet1").Range("A" & lrow) <> vbNullString
        lrow = lrow + 1
    Loop

I tried out using that second bit both with and without the originial edits that you suggested.

If I use it alone, i.e. the original code but with the

Sub GetCurrentData()
    Dim lrow As Long
    lrow = 3
    Do While Worksheets("Sheet1").Range("A" & lrow) <> vbNullString
        lrow = lrow + 1
    Loop

at the beginning, the macro runs fine with no errors, but as far as I can see in excel it doesen't do anything. No data is printed out into other cells which is a bit weird. I tried out using ' to take those lines out and it doesen't appear to make any difference to how the macro works.

Using the extra bit with the initial changes you suggested I get the same erorr appearing on the line as before
"Run-time Error 1004 - application-defined or object-defined error",
with the highlighed line being the same as before:

lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(x1Up).Row

It seems I have a tricky problem to solve!

Another GetCurrentData routine.

Sub GetCurrentData()
Dim lrow As Long
lrow = 3
Do While Worksheets("Sheet1").Range("A" & lrow) <> vbNullString
    lrow = lrow + 1
Loop
chan = DDEInitiate("PLW", "Current")
If TypeName(chan) = "Error" Then
    Repeat = False
    MsgBox "PicoLog cannot be found - Macro Halted!!"
Else
    returndata = DDERequest(chan, "Name")
    For i = LBound(returndata) To UBound(returndata)
        Worksheets("Sheet1").Cells(lrow + i, 1).Value = returndata(LBound(returndata) + i - 1, 1)
    Next i
    returndata = DDERequest(chan, "Value")
    For i = LBound(returndata) To UBound(returndata)
        Worksheets("Sheet1").Cells(lrow + i, 2).Value = returndata(LBound(returndata) + i - 1, 1)
    Next i
    returndata = DDERequest(chan, "Units")
    For i = LBound(returndata) To UBound(returndata)
        Worksheets("Sheet1").Cells(lrow + i, 3).Value = returndata(LBound(returndata) + i - 1, 1)
    Next i
    DDETerminate chan
End If