I have an Excel DDE file that updates periodically with quotes, portfolio values, etc. I want to periodically collect a snapshot of the DDE values and store the data for review. So for example let's say the day's high is dynamically updated into cell B1, I would like to have look at B1 at specified times, retrieve the value of B1 at that time and store it in say cell B2. I canât seem to find an Excel function or formula tat would achieve this. Any ideas or tips? Many Thanks!
Only one way I was able to solve this problem... A macro that will copy the values, and paste into another sheet every x minutes. I made a separate workbook that only does that b/c you have to leave the cursor on one cell and don't want to interfere with it. Also if you have it copying every one minute, you will find that you may copy some text in a word document, wait a little while, and CTRL+V and you will get the data from the workbook (if your macro triggers in the time between you pasted your text). Gets annoying, but get used to it. PM me if you want some help, I can probably email you the workbook I have and you can manipulate it to your liking.
Example Code: 2 sheets - one named "Charts" the other named "Raw" "Charts" -should include DDE input -row A is title headers -row B is the DDE data that will be recorded -make sure to save the file with the cursor on cell B1 (I suggest putting a time function in this cell like =now() "Raw" -paste the headers from "Charts" into row A -make sure to save with cursor in cell B1 Some pointers: Open a totally new Excel program...do not run within the same Excel as other workbooks and leave it minimized. This macro will only start when you hit Ctrl+a, and will ONLY END when you close that excel program completely (cannot just close the workbook and leave excel open..it will re-open this workbook automatically if you do that). MACRO CODE [Time value can be changed accordingly...it will re-record a minute from the time you start the macro with Ctrl+a] -------------------------------------------------- Sub record() Application.OnTime Now + TimeValue("00:01:00"), "record" ' record Macro ' Macro recorded 8/18/2006 by TradeNYSE ' ' Keyboard Shortcut: Ctrl+a ' ActiveCell.Rows("1:1").EntireRow.Select Selection.Copy Sheets("Raw").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(1, 0).Range("A1").Select Sheets("Charts").Select ActiveCell.Select End Sub
Have a look at this spreadsheet it basically saves the dde data in a time interval you specify. Have a look at the code in VB. Right click the start timer and end timer buttons and say edit code/macro.
Heres the relevant code Public Declare Function SetTimer Lib "user32" (ByVal HWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long Public Declare Function KillTimer Lib "user32" (ByVal HWnd As Long, ByVal nIDEvent As Long) As Long Public TimerID As Long Public TimerSeconds As Single Sub StartTimer() TimerSeconds = 600 'how often to "pop" the timer. TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc) End Sub Sub EndTimer() ' On Error Resume Next KillTimer 0&, TimerID End Sub Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long) Static time10 As Double Static j10 As Long 'On Error GoTo ErrorHandler j10 = j10 + 1 time10 = time10 + 1 realtime = 10 * time10 Range(Cells(10, j10 + 22), Cells(10, j10 + 22)).Value = realtime For i = 0 To 27 Range(Cells(i + 11, j10 + 22), Cells(i + 11, j10 + 22)).Value = Range(Cells(i + 16, 14), Cells(i + 16, 14)).Value Next i End Sub Basically it uses a timer to copy and paste the data. Each time it shifts the data over a few columns so as not to overwrite what it saved already.