Sorry to ask, but how do I pick up a cell value' at a specified time of day. I want to know the Emini value at, say 12:30 and I already have the Emini DDE in a cell.. ?? Don
depends on a lot of things. If the spreadsheet updates fairly frequently you can put some VBA code in the .calculate subroutine that checks the value against the time (either in a cell with =now() or against the windows API and system clock) and then copy it to another cell when you like. Alternatively, if you need more accurate results or your sheet doesn't update on it's own, you'll have to use the windows API to run a timer function that checks the time every n seconds (milliseconds) and then copies it when it's done. If you give me a bit more info, I might be able to find some old code you can "plug and play" with. Thanks, - The New Guy EDIT: I'm not sure if you edited that last post, of if I just can't read. If it's DDE and it's the SPU's it should probably be going at a pretty fast rate. Would it work if it basically went like this: copy the spu price at the first update after 12:30? Thanks, - The New Guy
I have a dde link into excel that has all the market data, auto update tick by tick. I have the emini's in a cell continually updating. I want another cell to "grab" the emini cell's value at a specific time of day. I can either use the computers clock or a "now" function if it updates. Yes, you have the idea ....what could I put into the formula bar to grab that number at various times of day.?? You can IM me at donbright@yahoo or donbright@aol if that's easier.. thanks..... Don
Hey Don. I have a couple of formulas to do that somewhere around here. If you don't get what you're lookin' for, let me know. I had a couple of programming guys code several formulas along those lines a year ago. I have it in another computer at the moment. risk'
Don, I have had good luck posting questions for help at this site. I usually get an answer within an hour or two. http://www.excelforum.com/forumdisplay.php?f=5
I could use that vba code too if you guys are passing it out. I want to do the same thing Don is asking. Thanks.
Hi Don, Put the next code in 'ThisWorkbook', this code activates the macro to copy paste at 12:30 when you open the sheet. Private Sub Workbook_Open() Call AutoUpdate MsgBox "Macro started" End Sub this is the code you should put in Module1. Sub AutoUpdate() Application.OnTime TimeValue("12:30:00"), "CopyData" End Sub Sub CopyData() Sheets("Sheet1").Select Range("A1").Copy Sheets(2).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Sheets("Sheet1").Select ActiveWorkbook.Save End Sub If your DDE link is in cell A1 on sheet1 this macro will copy it at 12:30 and paste in sheet2 in A1. Everyday it will paste the price below the previous day, so day 1 sheet2 cell A1, day 2 sheet2 cell A2 etc. Kind regards, Raver