Thread: Various macro issues linked to DDE

Hey everyone,

Apologies in advance for the long post, but I think I need to explain things carefully so people don't get confused!

A while ago I ventured onto these forums to seek some help regarding getting Excel to grab data from a datalogger and place it into columns on a worksheet. Since then I've been working on electronics, actually building the circuit and associated hardware, but now that the circuit works I've now moved back onto some programming to improve the Excel sheet I have currently to deal with the data.

The original post on these forums can be found here:
http://www.excelforum.com/excel-programming/639002-dde-macros-and-data-formatting.html

Now as the worksheet used to function it would collect the data every x seconds (set via VB, usually every 3 seconds, dependent on the button pressed), update the top left of the worksheet with the current results every x seconds, and at the same time, copy and paste the results into seperate columns (seperating each channel into a different column to make it easier to work with the data). This would then repeat every x seconds, placing each new set of data for each channel into the row below the last one, giving me a permanent record of all results taken so far. It then also placed a simple counter in one column (saying 1,2,3,4 etc down each row) showing which number in the sequence the data collected was. I then had a set of cells with formulae in them to work out a set of other variable from the original data obtained from the logger.
Having been playing around with the macro a bit recently, trying to make it do the things I want, I seem to have slightly broken it

I have attached an example worksheet to show you what it currently looks like, and with it is a small amount of sample data to show how it's working. Most of the macros are located in "Module 1", but some of the ones referred to at the end are in Module 2.

If I haven't confused you too much already, let me try to explain what I've been trying to make it do

-------- Simplify the 'interface' so that the whole thing can be triggered by one button press (as other people will be using this, and I want it to be as simple as it can be), and also monitored in one screen display (everything important can be seen without scrolling). To do this I made a new button (the "Start Readings" button), and attempted to create a macro do to the following things when pressed:

1. Clear the contents of the current data readings in the top left (range A4:C11)
2. Clear the contents of all cells in the range E17:W50000 [gets rid of all old data to make sure there is no mixing of sets of results]
3. Grab the latest set of data from the data logger, and place it in the top left area.
4. Look down the newly collected data in the top left, and use the Channel Names as cell contents as column titles from E17 going right until all channel names have been used (so the number of columns used could vary depending on the number of channels collected from the logger)
5. Then set the cells in row 17 to the right of the cells named in step 4 with a set of fixed titles (such as ALARM @150)
6. Then once all this has been set, call the macro which then collects the data from the logger every three seconds, and places it in the newly named columns, moving down a row with every new set of results.

The point of this set of steps is that when someone presses the Start button, it acts as a reset-all if you will, it clears out all old data, looks at the logger and re-tunes itself to be ready for importing the new data, then starts collecting it.

The problems I've encountered with these steps so far are:
1. works fine
2. I want the macro to look right down to the bottom row in Excel. This varies with different versions, and I have heard there is some kind of bottom row, or last row function, but having searched the web for a bit failed to find it. This would make sure it always clears out all the old data, by making it go right down to the bottom of the rows available. It's set at 50000 currently just because I can't find this function
3. works fine
4. Works, but I don't have the programming knowledge to make it more 'intelligent'; that is to make it only use the number of columns that there are channels, so that there aren't unessecary empty columns. It currently just creates a few empty columns that needn't be there. If lots more new channels are added, it also might not deal with them all as it may miss some off the ends.
5. Works, but same as for step 4: if the macro knew how many columns it was using in 4, it could then move these named columns right next to the data columns without gaps. There will be more columns in this area to come as the worksheet progresses.
6. This is where I seem to have really confused the macro! It now does quite a few strange things: places a batch of data at the top of the worksheet, where it isn't needed. The first row to use needs to be row 18, just below the titles for each column; it goes down a row every 3 seconds, but now ALL the rows update every three seconds with the new data - the old data is no longer preserved, so it now just progressively reads more and more rows all giving the same, changing results.

--------Adapt and extend the macro to add the calculated cells (the ones in columns R-W in the example attached, although there will be some more eventually) to be calculated and worked out as the macro goes along, rather than having all the values pre-placed in the cells manually, as this gives a lot less flexibility, and also means that you can't perform the "reset all" function I talked about above, as you lose the forumulae. There is also some conditional formatting associated with the two "Alarm" columns, which can be seen if you look at the cell's formatting. Ideally, if any of the cells in the two alarm columns flag up an alarm (and go red, displaying "Alarm"), then the large merged cell in D9:E15 would then display some formatting change to show an alarm has been detected, and perhaps pop up a message box too.

--------Ideally the graph at the top would also auto update with the new data as it comes in, automatically adjusting it's values when the sheet is reset etc. Currently I just placed the source data a long way down the sheet and it just displays the rows with values in, but this isn't a perfect solution.

--------Change the Time Elapsed column, which currently counts the number of the data record collected in each row, so that instead of simply counting the number (1,2,3 etc) it displays the current system time when each sample was taken. As it turns out (despite my old post) having the 'actual' time rather than just relative is actually more useful!

--------And if you want one final hurdle (which I've started with help from someone at work), if you take a gander in VB from Module 1 to Module 2, there will you find some code which is aimed at doing the following:
- as the data is recorded into the Excel file, it then also copies the results and places them in a comma-seperated text file, writing them in real time, to act as backup of the data in case Excel crashes or someone does something silly and deletes the data. The macro in this bit currently just counts up to a set time and displays a simple number count, also showing a system time stamp, and places it in a text file located in the same directory as the Excel file is located, with results comma seperated. It names the file currently "Testdd", with the dd being the current day of the month from the system time. Ideally it would name them the full date (yymmdd hhmm) because once we start collecting hundreds, an easily sortable file naming fomat will be invaluable!

Hmm, well this is indeed a long post with a lot of requests, and I've no doubt quite confusing, so apologies for you lot trying to understand it, if you need any more clarification then please ask and I'll try to explain it better!

As you can see I've got most of the areas started, and working to a certain extent, but my VB powers fail before getting it to be as 'slick' as I would really like it.

Any inputs on solving any of the issues mentioned is greatly appreciated!

**Edit: File was too large to attach, even zipped and reduced as much as I could. Have uploaded it to a sharing site, can hopefully be found here:
http://www.mediafire.com/?lzz0ujfz2yx

**Edit again: in the uploaded version, the graph is missing as I deleted it in an attempt to reduce the file size. The graph was set to display the two moving average columns of coil resistance change against time elapsed, as a nice visual representation of what's going on.

When you try something out, always do it on a backup copy. That way you still have the original working file to go with. Anyway, here is a, to start with, a partial solution for the variable items you want to check. So you start without headings in F17 and with additional check, they will add the channelnames you want to record at the time you start the macro.

Sub Put_In_Place()
'last row of channelnames
'use channelnames - 4 to know the offset column
'for your calculations
Dim mychannelnames As Long
Dim lastdatarow As Long
'normally I would use a reference for the worksheet
'but since you've got troubles with that
'you want to place everything in F:xx
'number of channels you want to record some info from
mychannelnames = 16 - Worksheets("sheet1").Range("A4:A16").SpecialCells(xlCellTypeBlanks).Count
'this is when you start without headings at F17
If Worksheets("sheet1").Range("F17") = vbNullString Then
    Worksheets("sheet1").Range(Worksheets("sheet1").Cells(17, 6), _
    Worksheets("sheet1").Cells(17, 6).Offset(, mychannelnames - 4)) = _
    Application.WorksheetFunction.Transpose(Worksheets("sheet1").Range("A4:A" & mychannelnames))
End If
lastdatarow = Worksheets("sheet1").Range("F" & Rows.Count).End(xlUp).Row
lastdatarow = lastdatarow + 1
Worksheets("sheet1").Range(Worksheets("sheet1").Cells(lastdatarow, 6), _
    Worksheets("sheet1").Cells(lastdatarow, 6).Offset(, mychannelnames - 4)) = _
    Application.WorksheetFunction.Transpose(Worksheets("sheet1").Range("B4:B" & mychannelnames))
'data to copy is always at B4:Bxx
'we need to change from top to bottom ---> left to right
'here we put a number for the seconds
If IsNumeric(Worksheets("sheet1").Range("F" & _
   Worksheets("sheet1").Range("F" & _
   Rows.Count).End(xlUp).Row).Offset(-1, -1).Value) Then
    Worksheets("sheet1").Range("F" & _
    Worksheets("sheet1").Range("F" & _
    Rows.Count).End(xlUp).Row).Offset(, -1).Value = _
    Worksheets("sheet1").Range("F" & _
    Worksheets("sheet1").Range("F" & _
    Rows.Count).End(xlUp).Row).Offset(-1, -1).Value + 1
Else
    Worksheets("sheet1").Range("F" & _
    Worksheets("sheet1").Range("F" & _
    Rows.Count).End(xlUp).Row).Offset(, -1).Value = 1
End If
End Sub

To find the last filled in column at row 17 you could search the last column from right to left.

Charlize

Another, possible usefull, coding example. This time we use the timestamp from E18 to save every row to a file. When you start a new logging, a new file will be created because E18 has another value.

Sub Put_In_Place() 'Macro for transposing the data from the top left
'(all in a chunk) into seperated columns per channel
Dim intStartRow As Long
Dim myfilename As String
'Sets the starting row to place the data in
intStartRow = Worksheets("sheet1").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Row
Worksheets("sheet1").Range("F" & intStartRow & ":M" & intStartRow) = _
Application.WorksheetFunction.Transpose(Worksheets("sheet1").Range("B4:B13"))
'B4:B13 is the Pico channel data to be copied
'Below: could do with some cleaning up, but this bit adds a time stamp
'(incremental for each new result)
    Worksheets("sheet1").Range("F" & _
    Worksheets("sheet1").Range("F" & _
    Rows.Count).End(xlUp).Row).Offset(, -1).Value = Split(Now(), " ")(1)
'Unique fileno for every log you start based on date + value of E18
'we use : for the timedisplay. Change to suit your needs
'we use dd/mm/yyyy hh:mm:ss
'save as yyyymmdd_hh-mm-ss.txt
'During the logging we always use E18 as the starting time
'next time, a new file will be created, when you start
'another logging. I suppose you're not going to use this
'from 23:59 to 00:01 ...
myfilename = Split(Split(Now, " ")(0), "/")(2) & _
             Split(Split(Now, " ")(0), "/")(1) & _
             Split(Split(Now, " ")(0), "/")(0) & "_" & _
             Split(Range("E18").Text, ":")(0) & "-" & _
             Split(Range("E18").Text, ":")(1) & "-" & _
             Split(Range("E18").Text, ":")(2) & ".txt"
Call subFileWrite(ActiveWorkbook.Path, myfilename, Range("E" & intStartRow & ":" & _
    "M" & intStartRow))
End Sub

This routine is the one that handles the creation of the string that we want to save to a file.

Sub subFileWrite(mypath As String, myfile As String, myrange As Range)
    Dim mycell As Range
    Dim mystring As String
    Dim fileno As Long
    For Each mycell In myrange
        mystring = mystring & mycell.Text & ";"
    Next mycell
    'remove last ; from string
    mystring = Left(mystring, Len(mystring) - 1)
    'use freefile to get a free file number to use
    fileno = FreeFile
    'use append instead of output so you could
    'record the changes of your picolog routine
    Open mypath & "\" & myfile For Append As #fileno
    'some coding to write info from picolog routine
    'to file
    Print #fileno, mystring
    Close #fileno
End Sub

Charlize