title: Populate cells dynamically using a data connection in Excel
body: <p>I am currently trying to handle a data connection in Excel and I would like to populate some cells dynamically as the data is periodically changing.</p>
  
  <p>More precisely, I have an Excel worksheet which is updated every 3 minutes through an internet data connection -stock market updates- during a time window of, say, 8 hours. Every 3 minutes the content of a cell (a decimal number), say, B1 is updated.</p>
  
  <p>I would like to know if it is possible to "record" every number B1 throughout the 8 hours in a column on my worksheet. 8*60/3=160, hence I would like to dynamically populate 160 cells with the content of cell B1.  </p>
  
  <p>If it is possible, I then would like to take the maximum out of these 160 cells (using the MAX(,) function), record that number in another column and plot the graph of the evolution of the value of B1 over the 8 hours time window. The idea is to do this on daily basis so that after a week or month I could be able to look at the worksheet and assess the market trends.  </p>
  
  <p>Following the answer and comments, I have tried to write some VBA subroutines but I am struggling to get them to actually work. Here is my code:</p>
  
  <pre><code>Dim RunTime As Date
  Dim j As Integer
  
  Sub CopyCell()
  Sheets("test").Cells(j, 3) = Sheets("test").Range("B1").Value
  j = j + 1
  'If Time &gt;= TimeSerial(20, 0, 0) Then'
      Application.OnTime RunTime, "CopyCell()", , False
  End If
  End Sub
  
  Sub Main()
  MsgBox "Starting Macro"
  j = 0
  RunTime = Now + TimeValue("00:03:00")
  Application.OnTime RunTime, "CopyCell()"
  End Sub
  </code></pre>
  
  <p>For the meantime I trigger the Main() sub myself and expect to make it stop at 20:0:0 (8pm). When I trigger the Main() sub I get the message "CopyCell() is not available in this workbook", but I do have copied the above code in the ThisWorkbook tab in the VBA editor and I have enabled all macros. Note that if I run the CopyCell() sub alone, the content of cell B1 is copied to cell C1.</p>
  
  <p>Can you help me figure out what is wrong in my code?</p>
  
  <p>Thanks,
  -David</p>