0

I have real-time data streaming from another application into Excel and making =RTD() calls successfully in a complex workbook.

Trying to (a) compare historical data as of a moment in time to current/live data, and, (b) run stats on snapshots taken. Say I have RTD data in 4 rows and 8 columns A1:H4.

  1. The "freeze": Is it possible to push a button (to run a macro) and render, say, row 2 (A2:H2) 'inert', so it's no longer RTD? Basically, copy then-current values and paste them into the same cells (A2:H2) as values, removing the RTD formulas so that the data stops changing at that moment.

  2. The "snapshot": Make a copy of the 3rd row, A3:H3, and paste it into A5:H5 periodically by timer, and have the copy become 'inert', i.e., no longer RTD or linked to RTD in row 5 while row 3 remains RTD. And place a timestamp for this action in cell I5 next to the copy.

This should also theoretically enable me to chart the snapshot data. I can't get RTD data to chart - expected that chart would update as streaming data changed, but charts won't display at all. So that's a secondary but related issue. It would appear that if I can snapshot the data and make it inert, I should be able to chart.

I've tried all kinds of manual processes from paste special/values, go to / special / formulas, highlight, etc. to using a third party tool. I've looked for VBA macros and found one which I'm pasting below, but I can't get it to work (the macro runs on cells to which it's directed, does not error out, but nothing happens). And even if it did work, it would only solve part of the problem as per above.

I do not speak VBA but write enough code in other languages that I should be able to figure out what it does and mod it, if someone is able to offer the base functionality. Any help would be greatly appreciated.

'This code should go in a regular module sheet, and won't work properly if installed anywhere     else. _ 
    The next (Dim) statement must occur before any subs or functions. 
 Dim NextTime As Double 

Sub RecordData() 
Dim Interval As Double 
Dim cel As Range, Capture As Range 
Interval = 5    'Number of seconds between each recording of data 
Set Capture = Worksheets("Sheet1").Range("A1:A5") 'Capture this column of data 
With Worksheets("Sheet2")   'Record the data on this worksheet 
    Set cel = .Range("A2")  'First timestamp goes here 
    Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0) 
    cel.Value = Now 
    cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Application.Transpose(Capture.Value) 
End With 

NextTime = Now + Interval / 86400 
Application.OnTime NextTime, "RecordData" 
End Sub 

Sub StopRecordingData() 
On Error Resume Next 
Application.OnTime NextTime, "RecordData", , False 
On Error GoTo 0

The above code is initiated when the workbook is opened, and stopped when the workbook is closed by code in ThisWorkbook code pane.

'These subs must go in ThisWorkbook code pane. They won't work at all if installed anywhere else! 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
StopRecordingData 
End Sub 

Private Sub Workbook_Open() 
RecordData 
End Sub 
End Sub
Ken White
  • 123,280
  • 14
  • 225
  • 444
KevinB
  • 1

1 Answers1

0

Explore using Application.RTD.ThrottleInterval = in your code to control the update speed. Excel defaults to 2000 (2 seconds) if never changed. Be aware that when you change it in code it becomes the new default so may want to change back on close of your project.

hlanier
  • 1