-
Aug 21st, 2011, 03:05 PM
#1
Thread Starter
New Member
[RESOLVED] Excel: recording data with Real Time Data Feed DDE
Hello, I'm new to VBA and this is my first post.
I have a simple problem: create a historical record from a live
on-line data feed on Excel.
Here is the problem:
1. Excel Workbook with 2 worksheets: "DataFeed" and "Record"
2. The "DataFeed" worksheet has 2 Cells with Data Feed from Internet
continuously updated every second:
Cell A1 = Time Cell A2 = Quote
3. The "Record" worksheet is empty and I need a VBA module to record
the content of Cells A1 and A2 (from "DataFeed" worksheet") to Columns
A and B every 5 minutes
If the VBA module starts working at 09:00:00 till 09:55:00 I will get
12 rows of data on "Record" worksheet:
On column A I will have: A1=09:00:00; A2=09:05:00; A3=09:10:00 ...
On column B I will have: B1=Quote at 09:00:00; B2=Quote at 09:05:00;
B3=Quote at 09:10:00 ...
I think the problem is clear and objective.
.
-
Aug 21st, 2011, 04:53 PM
#2
Re: Excel: recording data with Real Time Data Feed DDE
you can use application.ontime like
vb Code:
Sub update() With Sheets("record") rw = .cells(.rows.count, 1).end(xlup).row + 1 .Range(.Cells(rw, 1), .Cells(rw, 2)).Value = Sheets("datafeed").Range("a1:b1").Value End With Application.OnTime Now + TimeSerial(0, 5, 0), "update" ' run again after 5 minutes End Sub
to start recording, call update from anywhere, or workbook open event
you can call application.ontime again from elsewhere to to cancel the next update and stop further updating
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 22nd, 2011, 07:50 AM
#3
Thread Starter
New Member
Re: Excel: recording data with Real Time Data Feed DDE
Resolved!
Thank you "westconn1". I'm impressed with the elegant code. It's working
-
Nov 9th, 2012, 03:10 PM
#4
New Member
Re: [RESOLVED] Excel: recording data with Real Time Data Feed DDE
I have attempted to replicate this result, using the provided code. Perhaps I am attempted to call the update improperly, but when I Run the Update Macro in excel, after 5 minutes I get a message which says cannot run the macro ...... may not be available in workbook or macros may be disables. Macros are enabled. Any clue to what I am missing here?
-
Feb 8th, 2018, 04:38 AM
#5
Registered User
Re: Excel: recording data with Real Time Data Feed DDE
Originally Posted by westconn1
you can use application.ontime like
vb Code:
Sub update()
With Sheets("record")
rw = .cells(.rows.count, 1).end(xlup).row + 1
.Range(.Cells(rw, 1), .Cells(rw, 2)).Value = Sheets("datafeed").Range("a1:b1").Value
End With
Application.OnTime Now + TimeSerial(0, 5, 0), "update" ' run again after 5 minutes
End Sub
to start recording, call update from anywhere, or workbook open event
you can call application.ontime again from elsewhere to to cancel the next update and stop further updating
Can you please help for VB code for recording multiple cells from A1 to A10 in above example every one minute.
-
Feb 8th, 2018, 06:45 AM
#6
Re: [RESOLVED] Excel: recording data with Real Time Data Feed DDE
Code:
Sub update()
With Sheets("record")
rw = .cells(.rows.count, 1).end(xlup).row + 1
.Range(.Cells(rw, 1), .Cells(rw, 10)).Value = transpose(Sheets("datafeed").Range("a1:a10").Value
End With
Application.OnTime Now + TimeSerial(0, 1, 0), "update" ' run again after 5 minutes
End Sub
i did not test this, but it should add a1:a10 from datafeed sheet to a row on update sheet every one minute, hope that is what you wanted to do
this is a pretty old posting, probably you should have started a new thread
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|