Logging Data to Excel's Top Row
The macros we have previously given for automatically collecting data with Excel have all added data to the bottom of the spreadsheet. Today we explain how to log data to the top of the sheet, and move all the previously logged data down.
As a starting point we are using the macro given in Issue 57 of Monitor:
How to Log only when New Data Arrives.
To use this we have to add the line:
Cells(1, 1).EntireRow.Insert
The macro assumes that you are using Windmill software to continuously collect measurements and display them in the DDE Panel.
The method in full is as follows
- In Windmill DDE Panel select the Copy to Clipboard button and copy data from All Channels.
- From Excel's Edit menu choose Paste Special and "Paste as Links" into the first row of a worksheet called "Sheet1".
- Create the macro given below and run MonitorDDE.
The MonitorDDE routine monitors the spreadsheet which is being updated via DDE. When values change, it calls the LogData procedure which writes the new readings into the worksheet's top row.
You can copy and paste the below into your macro.
Sub MonitorDDE()
' Monitors the DDE links for updates
' When data is updated runs the subroutine LogData
ActiveWorkbook.SetLinkOnData "WINDMILL|Data!AllChannels", "LogData"
End Sub
Sub LogData()
NoOfRows = NoOfRows + 1
ddechan = DDEInitiate("WINDMILL", "Data")
' Requests readings from all the channels shown in the
' Windmill DDE Panel program and stores them in an array
' called mydata.
mydata = DDERequest(ddechan, "AllChannels")
' Ignores any warnings generated
On Error Resume Next
'Insert new row at top of sheet
Cells(1, 1).EntireRow.Insert
' Finds the lower & upper boundaries of array, to
' determine the number of columns needed to store
' the data.
Lower = LBound(mydata, 1)
Upper = UBound(mydata, 1)
' Inserts data from the array into a row of cells in a
' worksheet called Sheet1.
For Column = Lower To Upper
Sheets("Sheet1").Cells(1, Column).Value = mydata(Column)
Next Column
DDETerminate (ddechan)
End Sub
Many more Excel macros and tips are in our Excel section.
