VBA with real time data

J

jain.pb

Hi,

I am an amateur VBA programmer and need some help with real time data .
I am using the reuters tool to get the real time data. I intend to
capture this real time data in Excel every ten seconds for all the
stocks that i want to monitor and store it in an array(probably in a
different sheet) of size ten. This would mean that after 100 seconds
when all the cells of all the arrays are filled up the first cell in
each array should be cleared and assigned the current stock value.
This way each cell in the array should get a new value every 100
seconds.

Problem:- I have tried using Application.Ontime and Application .Wait
in a loop to capture these values every ten seconds. But when used in a
loop , the worksheet freezes meaning it stops recieving real time data.
And each cell of any particular array is filled with the same value as
the first cell. I need to somehow figure out how to introduce a delay
in the Sub without freezing the real time data feed . Here is a simple
code i'm trying to use. Please help me with this. I'll greatly
appreciate it.
---------------------------------------------------------------------------------------------------------------------------------------
Sub calling()
Dim i

For i = 1 To 10
Call dataCopy(i)
Call delay
Next

End Sub


--------------------------------------------------------------------------------------------------------------------------------------
Sub dataCopy(rec)
Dim i, j

j = rec

For i = 1 To 6
Worksheets("Sheet2").Cells(i, j).Value = Worksheets("Sheet1").Cells(i,
4)
Next

Exit Sub

End Sub
---------------------------------------------------------------------------------------------------------------------------------------
Sub delay()

Dim newHour, newMinute, newSecond, waitTime
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End Sub
 
J

jain.pb

Hi , its me again. I ma new to this group but I can somehow tell that
Tom is best one to help me with this. So, if you can please do reply
hopefully with a solution. If possible email it.Thank you.

Paresh
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top