Streaming with Visual Basic

[pad70]

Hi All,

I am trying to programme a Macro that takes values (F3:F35)from sheet ("Cover") every 5 minutes and displays the results on another sheet("dax history"). This is what I have so far:
------------------------------------------------------------
Option Explicit

Dim mdNextTime As Double

Sub StartIt()
Application.OnTime Now, "UpDateSub"
End Sub

Sub StopIt()
Application.OnTime mdNextTime, "UpDateSub", , False
End Sub

Sub UpDateSub()
Worksheets("Cover").Range("F3:F32").Copy _
Destination:=Worksheets("dax history").Range("B1:B30").End(xlToRight).Offset(, 1)

mdNextTime = Now + TimeValue("00:05:00")
Application.OnTime mdNextTime, "UpdateSub"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

-------------------------------------------------------

the cells F3:F32 update every few seconds so I want the macro to just paste the values every 5 minutes and move on to the next cell to the right.

Thanks for your help.

Paul.

 

[xlbo]

so what are you having problems with ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[pad70]

It doesn't seem to be working. I'm not sure why. I was wondering if anyone noticed something wrong in my code.

 

[SkipVought]

"It doesn't seem to be working"

What YOU mean by "It doesn't seem to be working," could be dozens of things.

Is the Code ERRORING?

Is the LOGIC incorrect? In which case WHAT is actually happening and WHAT do you think should happen?

Please be Clear, Concise and Complete.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pad70]

I want it to take values (F3:F32) from the 'Cover' Sheet, then paste the values in the 'dax history' sheet (B1:B30), then move over to (C1:C30), then paste values 5 minutes later.. and so on.

 

[ck1999]

Try changing to this

Sub UpDateSub()
Dim a
a = Sheets("dax history").Range("B1").End(xlToRight).Offset(, 1).Column
Worksheets("Cover").Range("F3:F32").Copy _
Destination:=Worksheets("dax history").Cells(1, a)
mdNextTime = Now + TimeValue("00:05:00")
Application.OnTime mdNextTime, "UpDateSub"

End Sub


ck1999

 

[ck1999]

There must be data in column b and c before the code will work with either the code you posted origionally or what I posted.

I think the xltoright is picking up the last possible column (if col b and c are empty) and then you are trying to add a column which gives you an error.

ck1999

 

[SkipVought]

The END method will ONLY work is there are values in each column...

   Worksheets("Cover").Range("F3:F32").Copy _
       Destination:=Worksheets("dax history").Range("B1:B30").End(xlToRight).Offset([red][b]0[/b][/red], 1)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pad70]

Thanks for all your help guys,

Just to clarify what I'm doing here,

I'm taking a constant stream from 'Reuters' of Stock Prices. The Stream of price is constantly updating. I'm trying to track each individual component of the Indicie (F3:F32 relates to the streamed price). I want the macro to take these prices and 'paste values' onto 'dax history' (The dax history sheet only has column A filled A1:A30 with names) sheet in (start with) column B (B1:B30) (A has the name of each component), then 5 minutes later paste the next corresponding values to column C (C1:C30) and so on for the day.

Does that make it clearer?

Thanks

Paul.

 

[SkipVought]

There is a fundamental flaw in your design.

In a 24 hour day there are 1440 minutes, divided by 5 yields 288 incriments.

Excel only has 256 columns.

Thats the physical limitation of Excel. But beyond that, the table design is not normalized. A much better design would put ALL the data into three columns: the two you start off with plus a date/time stamp.

   NextRow = Worksheets("dax history").Range("B1").End(xlTodown).Offset(1).row
   Worksheets("Cover").Range("F3:F32").Copy _
       Destination:=Worksheets("dax history").cells(nextrow, "B")
'then copy the row lables to A and the current date/time to C

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pad70]

Thanks for that,

Will that paste the values?

I don't know how to do that.

Thanks

 

[SkipVought]

I have the row headings range named SampleRowHeadings on a separate sheet

SampleRowHeadings.copy Destination:=Worksheets("dax history").cells(nextrow, "A")

then the Date/Time stamp...

with Worksheets("dax history")
  .range(.cells(nextrow, "C"), .cells(.[A1].currentregion.rows.count, "C")).value = now
end with

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Chance1234]

how are you accesing the reuters data ? is it through a feed ? if so why not just intercept the feed instead ?

Chance,

F, G + Its official, its even on a organisation chart et all

 

[kjv1611]

I'd be inclined to wonder, along with what Chance is saying, about the method behind this. Wouldn't it be easier to setup some sort of query or data connection to your source data instead of using VBA.

I know I've often tried to use VBA for solutions that were best handled with a SQL connection, as some of my previous posts here have alluded to.

--

"If to err is human, then I must be some kind of human!" -Me

 

[pad70]

It's a live feed so I figured pasting values every 5 minutes using a VB Macro would be the easiest option. I only need it for 8 hours a day so I just need to populate 96 cells a day and save the results.

 

[SkipVought]

"I just need to populate 96 cells a day and save the results."

What happends to the SAVED data?

Do you EVERY need to analyse or compare previously saved data?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}