Thread: 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

Thanks for your continued support!

I tried out that new GetData, and it doesn't come up with any errors, but again it doesn't seem to do anything on the excel worksheet itself.
The data still appears and updates every second, but it still replaces the old data with new in the same cells and doesn't copy it anywhere.

Now I really don't know how to program, so apologies for the most likely hideous code, but I had an idea about what might be possible....

Could you edit the GetCurrentData routine to include a copy and paste setup that repeats each second with the rest of the routine?

I had a go at writing some code having recorded a macro doing it, then editing the code and I came up with

Dim paster as Long
    paster = Worksheets("Sheet1").Range("A17")
    Range("B5:B14").Select
    Selection.Copy
    Range("paster").Select
    ActiveSheet.Paste
           paster = paster + 1
If Repeat Then Application.OnTime Now + TimeValue("00:00:01"), "GetCurrentData"

Now I tried that out and unsurprisingly it doesn't work, but I don't know if you think that might ever work? "paster" was just a name I came up with for the routine to select the cells where the data is input into, copy them, then paste them into other cells, then repeat with the rest of the routine, adding +1 rows/columns to the paste point each time. The range B5:B14 is where the data is currently put into by the macro. I juse chose A17 as a random choice of cell to paste it into.

Also, apologies if this is what you're trying to achieve, but instead of maybe asking it to copy and paste the new data each time, could it be set to input the data into a different column each time, so it automatically adds +1 column each time the series repeats, thus leaving the old data behind in the column 'behind' it?

A workbook 95 file ? Anyway, a file as attachment.

Charlize

Ah yes, the original file was in 95 format, but I'm using 2003 and it's converted up now.

But success! The macro is now working beautifully, recording away like anything.

I feel slightly pushing my limits for help at this point considering how much time you've put in, but if you feel the urge to do a little more then I have a couple of things...:

- when it copies each new set of data, it would be great if it could place a simple time count alongside in another column. Since the sampling period is always set at 1Hz, and the total time isn't needed, just the relative, all it needs to do is start at "1" when the first block of data is copied, and then go down sequentially with each block...2,3,4,5 etc, but if it's easier then placing the total system time (so long as it displays seconds) would be fine too, it really doesen't matter that much.

- it's now recording all the data fine, but one of the next steps in my project is to set all this up in a template Excel sheet with conditional formating and moving averages and other such things all in place (which I have ready), and the data simply slots in. Now to make this work properly I was under the impression that the data will have to be taken from the large block, and each seperate value (so "Voltage" or "Coil Resistance" etc. in this case) placed into its own column, so that the graph and other calculations can be told to simply look down the columns for their data. For the current number of channels that I have, the length of each 'block' of data is 10 rows. So to take the values for Coil Resistance for example you start at B27 and the next one is at B37, then B47 etc. I guess there is some way to tell Excel to copy each new value for the same input to a new column. Is it possible to tell it to look for, say, the Channel Name in a cell (such as "Temperature") and then every time it sees a certain Channel name it copies the cell to the right of it to another set column, then moves down a row for the next instance?

Feel free to tell me if I'm asking too much, but you seem to know what you're doing and you've done a great job so far!

Thanks.

Give an exact sample of the output you get when you perform a fetch for data starting at A4.
A4 = Channel 1, B4 = 24, C4 = °C
A5 = Cold Jonction, B5 = 24, C5 = °C

until you got all the possible names, readings and units. At least the names, cause those are going to be the headings for your data. How many channels do you have ?

Charlize

I've attached a version of the Worksheet with some blocks of sample data set out just as it comes from the logger software so you can see just how it's all set out.

It comes out to 10 channels in the end.

I think you gonna love this one. Instead of copying, put in directly in place at column F.

Charlize

Ah that's amazing! Exactly what I wanted, I do indeed love it!

Thank you very much Charlize.

I've now set up the sheet to work pretty much as I want it to now: new data is input into the columns, the coil change is converted into ppm, then two moving averages are taken of that change, one at 5 samples and one at 30 samples (with a graph plotting them as they go for reference), then the difference between the averages is taken, squared and square-rooted to remove any negative values, and a set of condionally formatted cells then look for values above a certain limit in the difference between the averages and if they see it, they turn red and print Alarm, otherwise go green and print OK.

Well now all that remins is to actually make my project work properly (lots of fun with noise and filtering issues in the circuits)!