Results 1 to 6 of 6

Thread: [RESOLVED] Excel: recording data with Real Time Data Feed DDE

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2011
    Posts
    6

    Resolved [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.

    .

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel: recording data with Real Time Data Feed DDE

    you can use application.ontime like
    vb Code:
    1. Sub update()
    2.  
    3. With Sheets("record")
    4.     rw = .cells(.rows.count, 1).end(xlup).row + 1
    5.     .Range(.Cells(rw, 1), .Cells(rw, 2)).Value = Sheets("datafeed").Range("a1:b1").Value
    6. End With
    7. Application.OnTime Now + TimeSerial(0, 5, 0), "update"   ' run again after 5 minutes
    8. 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

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2011
    Posts
    6

    Re: Excel: recording data with Real Time Data Feed DDE

    Resolved!

    Thank you "westconn1". I'm impressed with the elegant code. It's working

  4. #4
    New Member
    Join Date
    Nov 2012
    Posts
    1

    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?

  5. #5
    Registered User
    Join Date
    Feb 2018
    Posts
    1

    Re: Excel: recording data with Real Time Data Feed DDE

    Quote Originally Posted by westconn1 View Post
    you can use application.ontime like
    vb Code:
    1. Sub update()
    2.  
    3. With Sheets("record")
    4.     rw = .cells(.rows.count, 1).end(xlup).row + 1
    5.     .Range(.Cells(rw, 1), .Cells(rw, 2)).Value = Sheets("datafeed").Range("a1:b1").Value
    6. End With
    7. Application.OnTime Now + TimeSerial(0, 5, 0), "update"   ' run again after 5 minutes
    8. 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.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width