I am not familar with Betangel but I assume it will write updates straight into you worksheet. Hopefully that will trigger a worksheet change event. ( if not you will have to use a timer) but asssuming it does , the following code will copy the new data to the next available line and update data index.
I recommend having the data run down you spreadsheet rather than accross because you can see more rows of data than columns of data. However the same technique can be used to write it across the sheet if you want.
Also I recommend just appending the new data to the existing data for two reasons firstly it minimises the number of cells you need to update and thus the time taken. secondly you say you want to preserve the data for historic analysis.
The way to solve the graphing problem where you want to only look at the latest data is to create another table which uses the index ( In I2 in my example to pick up the latest dat ausing the offset function:
e.g
=OFFSET(A4,I$2-15,0)
=OFFSET(A4,I$2-14,0)
=OFFSET(A4,I$2-13,0)
=OFFSET(A4,I$2-12,0)
etc in successive cells down your spreadsheet
code to do the copying , you need to put this in the worksheet change event for the worksheet with the updating data
I assume the new data is arring in the ragne A2 to G2
Private Sub Worksheet_Change(ByVal Target As Range)If Not (Intersect(Target, Range("A2:G2")) Is Nothing) Then
' copy a1 to g1 to last row
Application.EnableEvents = False ' you must do this to prevent excel going into a loop
inarr = Range("a2:g2 ").Value ' pick up new data
ind = Cells(2, 9) ' pick up the index for the next location
Range(Cells(ind, 1), Cells(ind, 7)) = inarr 'write the data to a new line
Cells(2, 9) = ind + 1 ' increment the index
End If
Application.EnableEvents = True ' you must do this to ensure excel keeps working properly
End Sub